开发者

Update via subquery, what if the subquery returns no rows?

开发者 https://www.devze.com 2022-12-13 03:51 出处:网络
I am using a subquery in an UPDATE: UPDATE tableA SET x,y,z = ( (SELECT x, y, z FROM tableB b WHERE tableA.id = b.id

I am using a subquery in an UPDATE:

UPDATE tableA 
SET x,y,z = ( (SELECT x, y, z 
               FROM tableB b
               WHERE tableA.id = b.id
                 AND (tableA.x != b.x
                      OR tableA.y != b.y
                      OR tableA.z != b.z))) );

My question is, what happens if the subquery returns no rows? Will it do an update with nulls?

开发者_StackOverflow社区

Secondly, is there a better way to write this. I am basically updating three fields in tableA from tableB, but the update should only happen if any of the three fields are different.


what happens if the subquery returns no rows? Will it do an update with nulls?

Yes-- you can test this like:

update YourTable
set col1 = (select 1 where 1=0)

This will fill col1 with NULLs. In case the subquery returns multiple rows, like:

update YourTable
set col1 = (select 1 union select 2)

The database will generate an error.

Secondly, is there a better way to write this. I am basically updating three fields in tableA from tableB, but the update should only happen if any of the three fields are different.

Intuitively I wouldn't worry about the performance. If you really wish to avoid the update, you can write it like:

UPDATE a
SET x = b.x, y = b.y, z = b.z
FROM tableA a, tableB b 
WHERE a.id = b.id AND (a.x <> b.x OR a.y <> b.y OR a.z <> b.z)

The WHERE clause prevents updates with NULL.


On informix I used, a variation of Andomar's solution:

UPDATE a
SET x,y,z = ( (SELECT x, y, z 
               FROM tableB b
               WHERE tableA.id = b.id) )
WHERE tableA.id IN (SELECT fromTable.id
                    FROM tableA toTable, tableB fromTable
                    WHERE toTable.id = fromTable.id
                      AND ((toTable.x <> fromTable.x) 
                           OR (toTable.y <> fromTable.y)
                           OR (toTable.z <> fromTable.z))
0

精彩评论

暂无评论...
验证码 换一张
取 消