与某些表连接时，更新查询出现语法错误 [英] syntax error with update query when join with some table

问题描述

这是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.

这篇关于与某些表连接时，更新查询出现语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！