SQL MERGE更新或将值插入到同一个表中

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

Well I'm getting a missing ON keyword error and with no clue what so ever, also is there any other way to make it less clumsy

好吧，我得到一个缺少ON关键字错误，并且不知道是什么，也有任何其他方法，使它不那么笨拙

Help is very much appreciated.

非常感谢帮助。

2 个解决方案

#1

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

#2

The problem is that your MERGE syntax is incorrect. Your statement takes the form of:

问题是您的MERGE语法不正确。您的陈述采取以下形式：

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

but it should be of the form:

但它应该是这样的形式：

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

Why do you have the VALUES and vt clauses between your using and your on clauses? That's the incorrect syntax. Also, whilst you can use select * from tablename in the using clause, you could just use the tablename directly, since you're selecting all columns and all rows.

为什么你的using和on子句之间有VALUES和vt子句？那是不正确的语法。此外，虽然您可以在using子句中使用select * from tablename，但您可以直接使用tablename，因为您选择了所有列和所有行。

#1

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

#2