oracle的merge into语句能否同时操作两张表？

站长

2024年07月08日 08:39 · 阅读数 5

假如现在有两张表，A表和B表。可以把B表当做是备份表之类的。CREATE TABLE A(

ID NUMBER,
NAME VARCHAR2

)

CREATE TABLE B(

ID NUMBER,
NAME VARCHAR2,
VERSION NUMBER

)

假如现在对A表进行merge into操作，根据id匹配，when matched then的时候update A表对应的数据，when not matched then的时候把对应的数据insert A表，并且update B表的对应id的version（version+1）。

请问merge into可以实现这个功能吗？如果不行有没有别的实现sql呢？如果是mysql又能怎么写呢？

请指教，谢谢。

1个回答

test

2024-07-08

你可以通过使用触发器或把MERGE INTO操作和UPDATE操作组合在事务中来实现这个的功能。先创建触发器：


CREATE OR REPLACE TRIGGER trg_after_merge
AFTER INSERT OR UPDATE ON A
FOR EACH ROW
DECLARE
  v_version NUMBER;
BEGIN
  SELECT version INTO v_version FROM B WHERE id = :NEW.id;
  
  IF v_version IS NOT NULL THEN
    UPDATE B
    SET version = v_version + 1
    WHERE id = :NEW.id;
  ELSE
    INSERT INTO B (id, name, version)
    VALUES (:NEW.id, :NEW.name, 1);
  END IF;
END;
/

再使用MERGE INTO操作表A：

MERGE INTO A a
USING (SELECT id, name FROM some_source_table) s
ON (a.id = s.id)
WHEN MATCHED THEN
  UPDATE SET a.name = s.name
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (s.id, s.name);

在MySQL中，可以使用INSERT ... ON DUPLICATE KEY UPDATE语句实现类似的功能。但由于MySQL不支持在ON DUPLICATE KEY UPDATE子句中更新其他表，因此需要把操作组合在事务中：


START TRANSACTION;

INSERT INTO A (id, name)
SELECT id, name FROM some_source_table
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO B (id, name, version)
SELECT id, name, 1 FROM A
ON DUPLICATE KEY UPDATE version = version + 1;

COMMIT;

适合作为回答的

经过验证的有效解决办法
自己的经验指引，对解决问题有帮助
遵循 Markdown 语法排版，代码语义正确

不该作为回答的

询问内容细节或回复楼层
与题目无关的内容
“赞”“顶”“同问”“看手册”“解决了没”等毫无意义的内容