Hello Jim,
you may also perform this using two commands
- an multitable update first, then an insert.
Below is a simple example.
Remember that the update has to be run as first, then the insert ,
because the update command would update again the same rows just inserted a while ago by the insert ;)
This would work fine for small tables, for huge tables performance might be worse because queries must read whole tables twice.
Give it a try and you will see.
mysql> select * from t1;
+----+-------+-------+
| id | name | value |
+----+-------+-------+
| 1 | name1 | 2 |
| 2 | name2 | 2 |
| 3 | name3 | 2 |
+----+-------+-------+
3 rows in set (0.00 sec)
mysql> select * from t2;
+----+-----------+-------+
| id | name | value |
+----+-----------+-------+
| 2 | new name2 | 4 |
| 3 | new name3 | 3 |
| 4 | new name4 | 3 |
+----+-----------+-------+
3 rows in set (0.00 sec)
mysql> update t1, t2
-> set t1.name = t2.name, t1.value = t2.value
-> where t1.id = t2.id;
Query OK, 2 rows affected (0.09 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from t1;
+----+-----------+-------+
| id | name | value |
+----+-----------+-------+
| 1 | name1 | 2 |
| 2 | new name2 | 4 |
| 3 | new name3 | 3 |
+----+-----------+-------+
3 rows in set (0.00 sec)
mysql> insert into t1( id, name, value )
-> select id, name, value from t2 where t2.id not in ( select id from t1 );
Query OK, 1 row affected (0.10 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+-----------+-------+
| id | name | value |
+----+-----------+-------+
| 1 | name1 | 2 |
| 2 | new name2 | 4 |
| 3 | new name3 | 3 |
| 4 | new name4 | 3 |
+----+-----------+-------+
4 rows in set (0.00 sec)
Edited 4 time(s). Last edit at 02/25/2012 04:27AM by irek kordirko.