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
 )

• Dan Bracuk: The only comment I have is to that "not in" is slow. Look for a way to use either "not exists" or "in (select this except select that)".
• Johnny Bones: Make sure every field you're joining on is indexed.
• Mogambo: @DanBracuk If I do a straight swap of NOT IN with NOT EXISTS will that work? I have never seen NOT EXISTS used in a where clause before. Glad I posted this question :)
• Dan Bracuk: "Not in" and "Not exists" work differently and are not necessarily interchangeable. Look for examples of each and test different things.

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.

• Mogambo: Thanks!! My primary goal was to reduce redundancy & learn along the way :). Not only does this accomplish that, it is also very easy to understand despite my limited SQL knowledge. I'll have the spelling mistake in column name corrected as well :)