与某些表联接时更新查询的语法错误

2022-01-17 00:00:00 sql-update sql oracle inner-join

这是一个 API 生成的查询 - 不知道出了什么问题.

Here is an API generated query - Not sure what is wrong.

UPDATE T123 
SET COL1 = 1, VER1 = VER1 + 1  
INNER JOIN  
    SELECT C1 
    FROM (SELECT T.NUM_FLD C1 FROM WAPTDT_123 T) TAB ON C1 = REQUEST_ID

给我错误

SQL 命令未正确结束

SQL command not properly ended

所有列都存在于表中，我认为连接和在 oracle 上运行此命令有问题.

All columns are present in table, I believe something is wrong with the join and running this command on oracle.

编辑

还有一点，

SELECT C1 
FROM (SELECT T.NUM_FLD C1 FROM WAPTDT_123 T) TAB

当我从某些 API 获得时，部分查询已修复.

part of query is fixed as I am getting from some API.

推荐答案

Oracle不支持update语法中的join:

Oracle does not support join in the update syntax:

UPDATE T123
    SET COL1 = 1,
        VER1 = VER1 + 1
    WHERE EXISTS (SELECT 1 FROM WAPTDT_123 T WHERE T123.REQUEST_ID = T.NUM_FLD);

这是标准 SQL，应该适用于任何数据库.

This is standard SQL and should work in any database.

您的查询还有其他问题...子查询不在括号中，inner join 没有第一个表.

Your query has other problems as well . . . the subquery is not in parentheses, the inner join has no first table.

您可以使用该子查询编写此查询:

You can write this query with that subquery:

UPDATE T123
    SET COL1 = 1,
        VER1 = VER1 + 1
    WHERE T123.REQUEST_ID IN (SELECT C1 FROM ( SELECT T.NUM_FLD C1 FROM WAPTDT_123 T) TAB );

我将其切换为 IN，只是因为这是另一种选择.你仍然可以使用 EXISTS.

I switched this to an IN, just because that is another option. You could still use EXISTS.

相关文章