CODEWITHSUNDEEP
mysql
Dumitru Gutu
Dumitru Gutu

Reputation: 579

How to MERGE records in MYSQL

I'm able to use MERGE statement in both Oracle and MSSQL. Right now I have to use MYSQL. Does MYSQL has similar statement to merge data.

Lets say I have two tables:

create table source
(
 col1 bigint not null primary key auto_increment,
 col2 varchar(100),
 col3 varchar(50)
 created datetime
)

create table destination
(
 col1 bigint not null primary key auto_increment,
 col2 varchar(100),
 col3 varchar(50)
 created datetime 
)

Now I want move all data from "source" to "destination". If record already exists in "destination" by key I need update, otherwise I need insert.

In MSSQL I use the following MERGE statement, similar can be used in ORACLE:

MERGE destination AS TARGET
USING(SELECT * FROM source WHERE col2 like '%GDA%') AS SOURCE
ON
(TARGET.col1 = SOURCE.col1)
WHEN MATCHED THEN
UPDATE SET TARGET.col2 = SOURCE.col2,
           TARGET.col3 = SOURCE.col3
WHEN NOT MATCHED THEN
INSERT INTO
(col2,col3,created)
VALUES
(
SOURCE.col2,
SOURCE.col3,
GETDATE()
)OUTPUT $action INTO $tableAction;

WITH mergeCounts AS
(
SELECT COUNT(*) cnt,
     MergeAction
  FROM @tableAction
 GROUP BY MergeAction
) 
SELECT @Inserted = (SELECT ISNULL(cnt,0) FROM mergeCounts WHERE MergeAction = 'INSERT'),
     @Updated = (SELECT ISNULL(cnt,0) FROM mergeCounts WHERE MergeAction = 'UPDATE'),
     @Deleted = (SELECT ISNULL(cnt,0) FROM mergeCounts WHERE MergeAction = 'DELETE')

so here I'm update records if exists and insert if new record. After MERGE statement I also able to count how many records was inserted, updated ...

Does it possible to have such implementation in MYSQL ??

Upvotes: 0

Views: 86

Answers (1)

pala_
pala_

Reputation: 9010

Mysql has insert ... on duplicate key update ... syntax. use it like this:

insert into destination(col1, col2, col3, created) 
  select * 
    from source
  on duplicate key update
    col2 = values(col2),
    col3 = values(col3),
    created = values(created);

demo here

to get the number of affected rows, run select row_count() afterwards

Upvotes: 1

Related Questions