MySQL moving data between tables, if exist update

Question

I am trying to update one table from another in MYSQL, i did:

INSERT INTO new_table (id, last_name, first_name, etc) 
SELECT (id, last_name, first_name, etc) FROM uploaded_tmp
ON DUPLICATE KEY UPDATE

How do I instruct to update the values with the newer values?

or better "UPSERT", http://stackoverflow.com/questions/6107752/how-to-perform-an-upsert-so-that-i-can-use-both-new-and-old-values-in-update-par — matcheek, Nov 03 '14 at 10:18

David162795 · Accepted Answer · 2014-11-03T16:53:45.017

1

There is a nice feature called REPLACE

REPLACE INTO new_table (id, last_name, first_name, etc) 
SELECT (id, last_name, first_name, etc) FROM uploaded_tmp

It behaves just like INSERT, but if duplicate key happens (either PRIMARY or UNIQUE), the old record will be deleted and replaced with new values.

Interested? Read more on http://dev.mysql.com/doc/refman/5.7/en/replace.html

edited Nov 03 '14 at 16:53

answered Nov 03 '14 at 16:27

David162795

1,846
3
15
20

That's wrong. It is ALWAYS an insert. The old dataset will be deleted and a new one inserted. So if you use `AUTO_INCREMENT` on your `PRIMARY`, there will be a new incremented ID. – BreyndotEchse Nov 03 '14 at 16:40
As long as even the primary key is being replaced, it will be the same. If the primary key is not included within replacing data, yes, it will get new index, I should update my answer. – David162795 Nov 03 '14 at 16:47

score 1 · Answer 2 · answered Nov 03 '14 at 16:58

1

you are missing an update statement here.complete it with setting of some data

use :

INSERT INTO table(value)
SELECT id, uid
FROM tmp t WHERE uid=x
ON DUPLICATE KEY UPDATE ut=uy,yt=iu.....

reference :Mysql site

answered Nov 03 '14 at 16:58

Divya

1,469
1
13
25

score 1 · Answer 3 · answered Nov 03 '14 at 17:01

Are the IDs of new_table and uploaded_tmp the same? If not, remove them from the INSERT statement
Do you need the ID to be the same after the update? If not see the answer of David162795.
If you don't want your PRIMARY ID to change you have to use INSERT INTO ... ON DUPLICATE KEY UPDATE. But then we must know your UNIQUE indices

In this example there is a PRIMARY key (ID) and a UNIQUE index (last_name, first_name)

INSERT INTO new_table (
    last_name,
    first_name,
    val1,
    val2,
    ...
) 
SELECT
    last_name,
    first_name,
    val1,
    val2,
FROM uploaded_tmp
ON DUPLICATE KEY UPDATE
    val1 = VALUES(val1),
    val2 = VALUES(val2)

MySQL moving data between tables, if exist update

3 Answers3