在更新中使用子查询总是需要在where子句中使用子查询? [英] Using subquery in an update always requires subquery in a where clause?

问题描述

这对我来说是一个常见的SQL查询:

This is a common SQL query for me:

update table1 set col1 = (select col1 from table2 where table1.ID = table2.ID)
where exists (select 1 from table2 where table1.ID = table2.ID)

有什么办法可以避免拥有两个几乎相同的子查询?该查询是一个明显的简化，但是性能会受到影响，并且查询的内容不必要地混乱.

Is there any way to avoid having two nearly identical subqueries? This query is an obvious simplification but performance suffers and query is needlessly messy to read.

推荐答案

不幸的是，Informix不支持UPDATE语句中的FROM子句. 解决方法，您将获得更好的结果(性能)，是将UPDATE更改为MERGE语句.

Unfortunately Informix don't support the FROM clause at UPDATE statement. The way to workaround and you will get better results (performance) is change the UPDATE to MERGE statement.

这仅在您的数据库版本为11.50或更高版本时有效

This will work only if your database is version 11.50 or above

MERGE INTO table1 as t1
USING table2 as t2
   ON t1.ID = t2.ID
WHEN MATCHED THEN UPDATE set (t1.col1, t1.col2) = (t2.col1, t2.col2);

查看 IBM Informix手册有关更多信息

这篇关于在更新中使用子查询总是需要在where子句中使用子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！