0

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

Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
krifur
  • 870
  • 4
  • 16
  • 36
  • 1
    possible duplicate of [How do I update if exists, insert if not (aka upsert or merge) in MySQL?](http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql) – Mikael Eriksson Aug 23 '11 at 10:31
  • thx I'm going to mixed up with the Doug's solution – krifur Aug 23 '11 at 12:09

1 Answers1

2

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
ain
  • 22,394
  • 3
  • 54
  • 74
Doug Kress
  • 3,537
  • 1
  • 13
  • 19
  • thx i think I'm going to avoid the replace, but why is there no solution excepting the one listing all the fields ?Can't possible do it with simply a primary key ? – krifur Aug 23 '11 at 12:08
  • 1
    If you don't list the fields, they have to be all the same fields, in exactly the same order. If you write code now that doesn't list the fields, later one of the tables may be changed, and the routine would break. – Doug Kress Aug 23 '11 at 15:54