UPDATE ROWS FROM THE SAME TABLE

AS SALAMO ALAIKUM WA RAHMATULLAH

SELECT * FROM UPD_ROW

ID ITEMID ITEM QTY LOCATIONID
1 6 NOKIA-N30-B 2 5
2 7 NOKIA-N30-SL 1 5
3 8 NOKIA-N30-GL 10 5
4 9 IPH6-128-GL 10 5
5 10 IPH6-128-WH 1 5
6 11 IPH6-128-WH 10 135
7 11 IPH6-128-WH 5 5
8 9 IPH6-128-GL 11 135

9 8 NOKIA-N30-GL 20 135

UPDATE COMMAND
;WITH ITEM_QTY (ID,QT)
AS
(
SELECT ITEMID,QTY FROM UPD_ROW
WHERE LOCATIONID=5) 
UPDATE UPD_ROW
SET QTY=QTY+ITEM_QTY.QT
FROM ITEM_QTY

WHERE LOCATIONID=135 AND ITEM_QTY.ID=ITEMID
-- RESULT
ID ITEMID ITEM QTY LOCATIONID
1 6 NOKIA-N30-B 2 5
2 7 NOKIA-N30-SL 1 5
3 8 NOKIA-N30-GL 10 5
4 9 IPH6-128-GL 10 5
5 10 IPH6-128-WH 1 5
6 11 IPH6-128-WH 15 135
7 11 IPH6-128-WH 5 5
8 9 IPH6-128-GL 21 135

9 8 NOKIA-N30-GL 30 135

--
MA ASALAAM
PASSION 4 ORACLE