CODEWITHSUNDEEP
mysql
Dean
Dean

Reputation: 8065

MySQL moving data between tables, if exist update

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?

Upvotes: 0

Views: 72

Answers (3)

BreyndotEchse
BreyndotEchse

Reputation: 2230

  1. Are the IDs of new_table and uploaded_tmp the same? If not, remove them from the INSERT statement
  2. Do you need the ID to be the same after the update? If not see the answer of David162795.
  3. 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)

Upvotes: 1

Divya
Divya

Reputation: 1487

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

Upvotes: 1

David162795
David162795

Reputation: 1866

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

Upvotes: 1

Related Questions