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

sql-server, t-sql

Solution

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`

Problem

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?

Original source