-1

Is it possible to merge the following update queries into a single query?

        UPDATE 
        tag.ItemTagValue
        SET [Value] = @firstNode
        WHERE FkTagID = 6 and TagIndex = 1

        UPDATE tag.ItemTagValue
        SET [Value] = 'Unknown'
        WHERE FkTagID = 7 and TagIndex = 1

        UPDATE tag.ItemTagValue
        SET [Value] = 'Unknown'
        WHERE FkTagID = 8 and TagIndex = 1
  • Merging the last two should be easy. What is the objective? Merging may not be more efficient nor more readable. – paparazzo Nov 4 '15 at 10:37
-1

Yes, using a searched CASE expression.

Note that your third UPDATE is the same as the second in your example, btw.

UPDATE
    tag.ItemTagValue
SET
    [Value] = 
        CASE
            WHEN FkTagID = 6 THEN @firstNode
            WHEN FkTagID = 7 THEN 'Unknown'
            -- Whatever the third situation was supposed to be...
            ELSE [Value]
        END
WHERE
    TagIndex = 1
;
  • Sorry I edited the question. Third update was supposed to be for 8. Not 7. – mayooran Nov 4 '15 at 10:57
  • I'm guessing you can work out the third line then. The IN expression in the WHERE clause in Julien Vavasseur's answer should improve performance, if there's a lot of data to consider. – AakashM Nov 4 '15 at 11:20
  • No, this is not equivalent to the 3 updates in the question. This will update all rows with TagIndex = 1 (possibly millions) while the 3 updates will update only rows with (TagIndex = 1 AND FkTagID IN (6, 7, 8)). The (possibly) many rows with TagIndex = 1 and FkTagID different than 6, 7 and 8 will of course experience a dummy update Value=Value. The result thoug of the update may be different to the original 3 updates due to any update triggers. And the performance may be far more worse for a large table, even if the result is the same. – ypercubeᵀᴹ Nov 5 '15 at 6:50
  • @ypercube fair points. Your own answer would of course be welcome... – AakashM Nov 5 '15 at 8:51
4

You can use this query:

UPDATE tag.ItemTagValue
SET [Value] = case 
    when FkTagID = 6 then @firstNode 
    when FkTagID = 7 then 'Unknown'
    when FkTagID = 8 then 'Unknown'
end
WHERE FkTagID in (6, 7, 8) and TagIndex = 1;

FkTagID in (6, 7, 8) may be removed if they are the only possibilities but it may help if you have an index on FkTagID or other values for FkTagID that don't need update.

A more generic query looks like this:

UPDATE tag.ItemTagValue
SET [Value] = case 
    when FkTagID = 6 then @firstNode 
    when FkTagID = 7 then 'Unknown'
    when FkTagID = ... then ...
    else ... end
WHERE FkTagID in (6, 7, ...) and TagIndex = 1;
  • 1
    +1. And sorry for being pedantic but there is still (small but still) a possibility that this one update may produce a different state in the database than the original 3 updates (in case of any UPDATE triggers which will be fired once for this but 3 times for the original.) – ypercubeᵀᴹ Nov 5 '15 at 6:52
  • 1
    Good comment and useful warning. Although we don't have this level of details in this case. – Julien Vavasseur Nov 5 '15 at 6:55

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.