Update based on select to same table - AGAIN, and AGAIN
14,206
Not 100% sure what you're trying to do - but most likely, you could write your UPDATE
statement much simpler - something like this:
UPDATE t1
SET t1.col2 = 10 * (t2.col1 + t2.col2)
FROM theTable t1
INNER JOIN theTable t2 ON t1.busRelAccount = t2.busRelAccount
WHERE
t1.rowName = 'INVENTORY'
AND t2.rowName = 'POS'
;
Basically, you define a JOIN between the two tables (both being the same table, really) and you then define how to update the column of t1
based on columns from t2
Author by
Blue high Mountain
Updated on June 04, 2022Comments
-
Blue high Mountain about 2 years
In Microsoft SQL Server:
I have seen this question several times, but can't see anyone solving the problem:
UPDATE theTable t1 SET t1.col2 = (SELECT (10 * (col1 + col2)) FROM theTable t2 WHERE t1.busRelAccount = t2.busRelAccount AND t2.rowName = 'POS' ) WHERE t1.busRelAccount = t2.busRelAccount AND t1.rowName = 'INVENTORY' ;
Anyone having solved this problem without using CURSOR and loop the table?