3
\$\begingroup\$

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
    )
\$\endgroup\$

migrated from stackoverflow.com Sep 19 '14 at 1:47

This question came from our site for professional and enthusiast programmers.

  • 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 '14 at 18:48
  • \$\begingroup\$ Make sure every field you're joining on is indexed. \$\endgroup\$ – Johnny Bones Sep 18 '14 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 '14 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 '14 at 19:08
2
\$\begingroup\$

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.

\$\endgroup\$
  • \$\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 '14 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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