CODEWITHSUNDEEP
sql-servert-sql
Blue high Mountain
Blue high Mountain

Reputation: 67

Update based on select to same table - AGAIN, and AGAIN

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?

Upvotes: 3

Views: 14229

Answers (1)

marc_s
marc_s

Reputation: 755491

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

Upvotes: 6

Related Questions