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

发布于 2024-08-12 22:52:40 字数 396 浏览 4 评论 0原文

我在更新中使用子查询：

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

我的问题是，如果子查询不返回任何行，会发生什么？它会用空值进行更新吗？

其次，有没有更好的写法。我基本上是从表 B 更新表 A 中的三个字段，但只有在这三个字段中有任何一个不同时才应进行更新。

原文

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?

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寻梦旅人 2024-08-19 22:52:40

如果子查询返回会发生什么
没有行？它会进行更新吗
空值？

是的——您可以这样测试：

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

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

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

数据库将生成错误。

其次，有没有更好的方法
写这个。我基本都在更新
表A中的三个字段来自表B，
但只有在以下情况下才会进行更新
这三个字段中的任何一个都不同。

直觉上我不会担心性能。如果您确实希望避免更新，可以这样写：

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)

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

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.

回复收藏 0 原文

小嗲 2024-08-19 22:52:40

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

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 原文

~没有更多了~