Update Field based on another table's values

Question

Is there a more elegant way to write the following Microsoft SQL Server 2008 command?

UPDATE TableB
SET TableBField2=0
WHERE TableBID IN(
     SELECT TableBID 
     FROM TableB
     JOIN TableA on TableB.TableAID=TableA.TableAID
     WHERE TableBField2 < 0
     AND TableAField1 = 0
)

In plain speak, what I'm doing is updating a table based on the value of a field in a joined table. I wonder if my use of IN() is considered inefficient.

If you can determine this data from facts elsewhere in the system, then why store it twice? Now you will have to run this update every time you touch either TableA or TableB because the computed result could have changed and therefore TableBField2 will be potentially out of date. — Aaron Bertrand, Nov 19 '09 at 16:55

score 1 · Accepted Answer · answered Nov 19 '09 at 16:41

1

This should be more efficient:

UPDATE TableB b
SET TableBField2=0
WHERE exists (
     SELECT 1
     FROM TableA
     WHERE b.TableAID=TableA.TableAID
     AND b.TableBField2 < 0
     AND TableAField1 = 0
)

answered Nov 19 '09 at 16:41

palindrom

18,033
1
21
37

I think I'll put the WHERE b.TableBField2 < 0 before the exists – Phillip Senn Nov 19 '09 at 18:38

score 1 · Answer 2 · answered Nov 19 '09 at 16:43

1

You can try something like this

UPDATE TableB
SET Field2 = 0
FROM    TableB b INNER JOIN
        TableA a ON b.TableB.TableAID=a.TableAID
WHERE b.Field2 < 0     
AND a.Field1 = 0

answered Nov 19 '09 at 16:43

Adriaan Stander

162,879
31
289
284

Update Field based on another table's values

2 Answers2

Linked