MySQL - How to insert or update from source table to target table with different Keys?

Question

I have 2 tables with different structures.

CREATE TABLE test1
(
    id INTEGER PRIMARY KEY,
    EmpName VARCHAR(50),
    Empid INTEGER
);

CREATE TABLE test2
(
    Empid INTEGER PRIMARY KEY,
    EmpFName VARCHAR(50),   
    EmpLName VARCHAR(50)
);

Is there a way to insert rows from test2 table into test1? If the row exists in test1 it should update the row as well. I think it's possible with Merge statement but it's not available for MySQL. Is there a similar function like this in MySQL?

I've looked into INSERT ... ON DUPLICATE KEY UPDATE but the tables should have the same primary keys.

It has many way to solve this problem. But I think Trigger is your answer. — Anukul Limpanasil, Apr 28 '21 at 04:14
Have you looked at [Updatable Views](https://dev.mysql.com/doc/refman/8.0/en/view-updatability.html)? — Johannes H., Apr 28 '21 at 04:14
check this https://stackoverflow.com/questions/15383852/sql-if-exists-update-else-insert-into — Chas, Apr 28 '21 at 04:17

score 0 · Answer 1 · answered Apr 28 '21 at 04:52

0

I think this can help you.

Create Trigger to detect when insert/update data in your table test2.
Inside Trigger use REPLACE INTO to change data in your table test1.

Please check this link for additional of REPLACE INTO command.

answered Apr 28 '21 at 04:52

Anukul Limpanasil

692
7
10

score 0 · Answer 2 · answered Apr 28 '21 at 05:17

Is there a way to insert rows from test2 table into test1? If the row exists in test1 it should update the row as well

UPDATE test1
JOIN test2 USING (Empid)
SET test1.EmpName = CONCAT_WS(' ', EmpFName, EmpLName)

Pay attention - EmpName max length is 50 whereas total length of combined name may be up to 50+1+50=101, so the combined value may be truncated. Increase max length for EmpName.

If you need to perform this operation automatically when the data in test2 is inserted/updated then use AFTER INSERT and AFTER UPDATE triggers, like

CREATE TRIGGER tr
AFTER INSERT -- and the same trigger on AFTER UPDATE event
ON test2
FOR EACH ROW
UPDATE test1 
  SET EmpName = CONCAT_WS(' ', NEW.EmpFName, NEW.EmpLName)
  WHERE test1.Empid = NEW.Empid;

MySQL - How to insert or update from source table to target table with different Keys?

2 Answers2