通过子查询更新，如果子查询没有返回任何行，该怎么办？

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?

我的问题是，如果子查询没有返回任何行会发生什么？它是否会使用null进行更新？

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.

其次，有没有更好的方法来写这个。我基本上从tableB更新tableA中的三个字段，但只有当三个字段中的任何一个不同时才会发生更新。

2 个解决方案

#1

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

如果子查询没有返回任何行，会发生什么？它是否会使用null进行更新？

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:

这将用cols填充col1。如果子查询返回多行，例如：

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.

其次，有没有更好的方法来写这个。我基本上从tableB更新tableA中的三个字段，但只有当三个字段中的任何一个不同时才会发生更新。

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.

WHERE子句阻止使用NULL进行更新。

#2

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

在我使用的informix上，Andomar解决方案的变体：

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))

#1