November 11, 2009 at 10:20 am
Any way we could combine the two update statements into one? Any help will be greatly appreciated.
UPDATE PURC_ORDER_LINE SET USER_ORDER_QTY ='5' WHERE PART_ID = '2-520129-2' AND PURC_ORDER_ID = '2458677';
UPDATE PURC_ORDER_LINE SET USER_ORDER_QTY ='135' WHERE PART_ID = '2-520129-2' AND PURC_ORDER_ID = '8675309';
November 11, 2009 at 10:30 am
It's not really any prettier:
UPDATE PURC_ORDER_LINE
SET USER_ORDER_QTY = CASE
WHEN PURC_ORDER_ID = '2458677' THEN '5'
WHEN PURC_ORDER_ID = '8675309' THEN '135'
ELSE USER_ORDER_QTY
END
WHERE PART_ID = '2-520129-2';
November 11, 2009 at 10:39 am
I don't recommend dsdeming's solution since it will set all other values for column USER_ORDER_QTY to NULL that don't match the CASE condition!!
@dsdeming: you might want to change the code you posted. It's dangerous to those who just run it without proper testing!! Comment does no longer apply. Code in question has been modified.
I'd rather use
UPDATE PURC_ORDER_LINE
SET USER_ORDER_QTY =
CASE PURC_ORDER_ID
WHEN '2458677' THEN '5'
WHEN '8675309' THEN '135'
ELSE USER_ORDER_QTY
END
WHERE PART_ID = '2-520129-2'
AND (PURC_ORDER_ID = '2458677' OR PURC_ORDER_ID = '8675309');
You could leave out the second search condition (AND (PURC_ORDER_ID = '2458677' OR PURC_ORDER_ID = '8675309')) but that would lead to updates that are not required (update rows with its original value...)
November 11, 2009 at 10:47 am
Thanks, Lutz.
That was careless of me. Any time you're using a CASE statement, it should explicitly deal with each possible case, and I obviuosly didn't.
November 11, 2009 at 11:21 am
Thank you very much. It is greatly appreciated. Learn something new each day!
November 11, 2009 at 2:27 pm
The original post is much easier to understand. Consider someone looking at or modifying the code a year from now, and the risk of introducing bugs or errors. Simpler is better sometimes.
November 11, 2009 at 2:53 pm
homebrew01 (11/11/2009)
The original post is much easier to understand. Consider someone looking at or modifying the code a year from now, and the risk of introducing bugs or errors. Simpler is better sometimes.
But: sometimes performance counts more than to easily understand the code. 😀
The two separate updates will touch the table twice. The CASE solution just once.
So, as usual, "it depends". 😉
Side note: I don't consider the code using the CASE clause as being complicated at all...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply