mysql - 如何在MySQL中使用合并功能？

Question

我对 sql 查询还是个新手。

我的表查询如下:

CREATE TABLE Department (
    D#      NUMBER(5)   NOT NULL, /* Department number      */
    DName       VARCHAR2(30)    NOT NULL, /* Department name        */
    Manager#    CHAR(5)     NOT NULL, /* Department manager number  */
    MSDate      DATE,             /* Manager start date         */
    CONSTRAINT Department_PK PRIMARY KEY(D#),
    CONSTRAINT Department_CK UNIQUE(DName)
);
INSERT INTO Department VALUES (1, 'SALES', '00110', TO_DATE('02/01/2012', 'DD/MM/YYYY'));
INSERT INTO Department VALUES (2, 'ACCOUNTING', '00120', TO_DATE('30/10/2010', 'DD/MM/YYYY'));
INSERT INTO Department VALUES (3, 'GAMES', '00150', TO_DATE('01/03/2008', 'DD/MM/YYYY'));
INSERT INTO Department VALUES (4, 'HUMAN RESOURCES', '00200', TO_DATE('02/01/2013', 'DD/MM/YYYY'));
INSERT INTO Department VALUES (5, 'SPORTS', '00250', TO_DATE('10/05/2010', 'DD/MM/YYYY'));

我将实现 sql 查询，例如“SPORTS”部门将合并到“GAMES”部门。 “游戏”部门的经理仍然是该部门的经理。在“体育”部门工作的员工留在同一地点。

MERGE INTO department d
USING (
  SELECT Manager#,MSDate
  FROM department d
ON (d.D# = 'GAMES')
WHEN MATCHED THEN
  UPDATE SET d.Manager# = d.Manager#
WHEN NOT MATCHED THEN
  ...

*

不知何故，我曾尝试使用此查询进行尝试，但不幸的是它不起作用 :( 如果有任何帮助，我非常感谢。

Answer 1

MySql 不支持MERGE

看看INSERT ... ON DUPLICATE KEY UPDATE Syntax

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.