有 Java 编程相关的问题?

你可以在下面搜索框中键入要查询的问题!

java SQL合并以更新值或将值插入到同一个表中

"MERGE INTO NT_PROPERTY ntProp USING ( "  +
                            "SELECT * FROM NT_PROPERTY ) " +
                            "VALUES " +
                                    "('minDPTObjectId'," + minDPTObjectId + ", 'Starting DPT Object Id') " +
                                    "('maxDPTObjectId', " + maxDPTObjectId + ", 'Ending DPT Object Id') " +
                            "vt (NAME, VALUE, NOTE) " +
                            "ON ( ntProp.NAME = vt.NAME ) " +
                            "WHEN MATCHED THEN " +
                            "UPDATE SET VALUE = vt.VALUE "+
                            "WHEN NOT MATCHED THEN " +
                            "INSERT (NAME, VALUE, NOTE) VALUES (vt.NAME, vt.VALUE, vt.NOTE)";

好吧,我得到了一个缺少关键字的错误,而且不知道究竟是什么,还有没有其他方法可以让它不那么笨拙

非常感谢您的帮助


共 (2) 个答案

  1. # 1 楼答案

    问题是您的合并语法不正确。您的声明采用以下形式:

    MERGE INTO nt_property ntprop
      USING (SELECT * FROM nt_property)
        VALUES (...)
        vt (...)
      ON (ntprop.name = vt.name)
    WHEN MATCHED THEN
      UPDATE ...
    WHEN NOT MATCHED THEN
      INSERT ...;
    

    但它的形式应该是:

    MERGE INTO target_table tgt_alias
      USING source_table_or_subquery src_alias
        ON (<JOIN conditions>)
    WHEN MATCHED THEN
      UPDATE ...
    WHEN NOT MATCHED THEN
      INSERT ...;
    

    为什么在你的usingon子句之间有VALUESvt子句?这是错误的语法。此外,虽然您可以在using子句中使用select * from tablename,但您可以直接使用tablename,因为您选择的是所有列和所有行

  2. # 2 楼答案

      MERGE INTO NT_PROPERTY D  
          USING (SELECT * FROM DUAL ) S  
          ON (D.NAME = 'minDPTObjectId')  
          WHEN MATCHED THEN UPDATE SET D.VALUE =   '1234' 
          WHEN NOT MATCHED THEN INSERT (NAME, VALUE, NOTE) 
          VALUES ('maxDPTObjectId', '1111', 'Ending DPT Object Id') ;