MySQL Forums
Forum List  »  Sybase

Re: Insert Ignore / On Duplicate Key - Conversion
Posted by: irek kordirko
Date: February 25, 2012 10:08AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Insert Ignore / On Duplicate Key - Conversion
3406
February 25, 2012 10:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.