CODEWITHSUNDEEP
mysqlsqlinsertconditional-statementssql-update
krifur
krifur

Reputation: 890

How to update a row from one table to another?

I try to update some rows of a table to a different table, for now on I just find out how to insert a row from one table to another, like:

INSERT INTO dialecte_org_5.tb_data_iae (
    SELECT * FROM dialecte_org_88.tb_data_iae WHERE id_dialecte = 2413
);

What could be the same statement but for an update? Is there a way to make something like this, if row exists -> "update", if not "insert"

Thx

Upvotes: 0

Views: 231

Answers (1)

Doug Kress
Doug Kress

Reputation: 3537

For an insert, You usually want to specify the full field list for both the insert statement and the select (http://dev.mysql.com/doc/refman/5.5/en/insert.html):

INSERT INTO dialecte_org_5.tb_data_iae (field1, field2, field3)
SELECT field1, field2, field3
FROM dialecte_org_88.tb_data_iae WHERE id_dialecte=2413;

And an update statement is more like this (http://dev.mysql.com/doc/refman/5.0/en/update.html):

UPDATE dialecte_org_5.tb_data_iae t1
INNER JOIN dialecte_org_88.tb_data_iae t2 ON t1.id = t2.id
SET t1.field1 = t2.field1, t1.field2 = t2.field2, t1.field3 = t2.field3
WHERE t2.id_dialecte=2413

You also can use REPLACE INTO, which does both, but is MySQL specific, not supported by other RDBMS's (http://dev.mysql.com/doc/refman/5.0/en/replace.html):

REPLACE INTO dialecte_org_5.tb_data_iae t1
INNER JOIN dialecte_org_88.tb_data_iae t2 ON t1.id = t2.id
SET t1.field1 = t2.field1, t1.field2 = t2.field2, t1.field3 = t2.field3
WHERE t2.id_dialecte=2413

Upvotes: 2

Related Questions