SQL update statement

Question

I have a SQL update statement which does what it's supposed to. However, I have a feeling that the way I wrote it isn't the best. I am not highly experienced in SQL so any pointers or hints on how can I rewrite this statement and possibly get rid of the duplicate joins (joining on same tables again in the where statement is what bothers me about this update statement) would be helpful.

UPDATE Item SET AttachementID = NULL
    FROM Item i
    JOIN BidItem ebi ON i.BidItemID = ebi.BidItemID
    JOIN Group eg ON ebi.GroupID = eg.GroupID
    WHERE eg.EstimateID = @JobID and i.AttachementID NOT IN (
        SELECT i.ItemID FROM Item i
            JOIN BidItem ebi ON i.BidItemID = ebi.BidItemID
            JOIN Group eg ON ebi.GroupID = eg.GroupID
            WHERE eg.EstimateID = @JobID
    )

Answer 1

I don't recommend the use of a FROM clause with UPDATE, as it is non-standard SQL. Neither Oracle nor MySQL supports UPDATE … FROM …. PostgreSQL does support FROM, but the documentation states that it is a PostgreSQL extension. Even worse, it interprets the FROM clause differently from Microsoft SQL Server! (PostgreSQL treats the Item i in the FROM clause as a self-join with the Item table in the main clause of the UPDATE; SQL Server treats it as an alias of the same table.)

I suggest using a Common Table Expression to reduce the redundancy. CTEs with UPDATE are still non-standard, but at least PostgreSQL and SQL Server interpret the query in the same way.

WITH ItemsForEstimate AS (
    SELECT ItemID
        FROM Item
            INNER JOIN BidItem
                ON BidItem.BidItemID = Item.BidItemID
            INNER JOIN [Group]
                ON [Group].GroupID = BidItem.GroupID
        WHERE EstimateID = @EstimateID
)
UPDATE Item SET AttachementID = NULL
    WHERE
        ItemID IN (SELECT ItemID FROM ItemsForEstimate)
        AND AttachementID NOT IN (SELECT ItemID FROM ItemsForEstimate);

Since GROUP is an SQL keyword, it should be quoted as an identifier. (I'm surprised that your query worked at all.)

AttachementID should be spelled AttachmentID.