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
  • 1
    \$\begingroup\$ 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)". \$\endgroup\$
    – Dan Bracuk
    Sep 18, 2014 at 18:48
  • \$\begingroup\$ Make sure every field you're joining on is indexed. \$\endgroup\$
    – Johnny Bones
    Sep 18, 2014 at 18:49
  • \$\begingroup\$ @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 :) \$\endgroup\$
    – Mogambo
    Sep 18, 2014 at 18:54
  • \$\begingroup\$ "Not in" and "Not exists" work differently and are not necessarily interchangeable. Look for examples of each and test different things. \$\endgroup\$
    – Dan Bracuk
    Sep 18, 2014 at 19:08

1 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 (
        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
        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.

  • \$\begingroup\$ 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 :) \$\endgroup\$
    – Mogambo
    Sep 19, 2014 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.