Update Field based on another table's values

2019-08-28 12:20发布

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.

2条回答
倾城 Initia
2楼-- · 2019-08-28 13:14

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
)
查看更多
▲ chillily
3楼-- · 2019-08-28 13:18

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
查看更多
登录 后发表回答