CODEWITHSUNDEEP
mysqldatabasemysql-workbench
user9371654
user9371654

Reputation: 2398

How to use UPDATE in INSERT SELECT statement

I want to insert records from table2 to table1. I need to replace the records if a duplicate in the primary key is found. The primary key of a type string.

So my statement look like (name is primary key and it is a string. Duplicate rows can arise from table2, which I want to update):

INSERT INTO `myscheme1`.`table1`
(`table1`.`name`,
    `table1`.`id`,
    `table1`.`history`)

SELECT `table2`.`name`,
    `table2`.`id`,
    `table2`.`history`
FROM `myscheme2`.`table2`;

Where to add UPDATE? If I can not use UPDATE, can you clarify how to use ON DUPLICATE KEY UPDATE?

Because I do not get how to apply it? I do not know the new string of the primary key that I should update the old one with (it is being read by the command and I have many duplicate cases). So this example: below assumes I know what is the new value that I should input if a duplicate happens.

I prefer to use UPDATE so it replaces the old record with the new automatically. 

INSERT INTO 
   devices(name) 
VALUES 
   ('Printer') 
ON DUPLICATE KEY UPDATE name = 'Printer';

Can you please show me how to edit my command syntax if I want to use UPDATE or ON DUPLICATE KEY UPDATE?

Upvotes: 1

Views: 129

Answers (1)

Barmar
Barmar

Reputation: 780655

You use ON DUPLICATE KEY UPDATE the same way whether the new values are coming from SELECT or VALUES.

INSERT INTO `myscheme1`.`table1`
(`table1`.`name`, `table1`.`id`,  `table1`.`history`)
SELECT `table2`.`name`, `table2`.`id`, `table2`.`history`
FROM `myscheme2`.`table2`
ON DUPLICATE KEY UPDATE id = VALUES(id), history = VALUES(history);

If table1 has the same name as a row in table2, the other columns will be copied into that row.

You don't need to include the name column in ON DUPLICATE KEY. It already has the value from the other table, since that's what makes it a duplicate.

Upvotes: 1

Related Questions