0

I have this customer table:

  • id = primary key
  • driver_id = unique int
  • user_id = foreign key to another table

Table:

+----+-----------+---------+
| id | driver_id | user_id |
+----+-----------+---------+
|  1 |    111111 |       4 |
|  2 |   1212121 |      23 |
|  3 |    412444 |      21 |
|  4 |     12341 |      21 |
+----+-----------+---------+

I have to insert multiple rows at once. However, I understand if I use INSERT INTO IGNORE and a driver_id already exists it won't add it. However, I want it so, if driver_id already exists, it updates the user_id.

INSERT IGNORE INTO customer (driver_id, user_id) VALUES
(5555, 43),
(111111, 63)

Therefore the end table would look like the following because it inserts the driver_id 5555 and updates the driver_id 111111 to user_id 63:

+----+-----------+---------+
| id | driver_id | user_id |
+----+-----------+---------+
|  1 |    111111 |      63 |
|  2 |   1212121 |      23 |
|  3 |    412444 |      21 |
|  4 |     12341 |      21 |
|  5 |      5555 |      43 |
+----+-----------+---------+

How can I achieve this? So Insert into a MySQL table or update if exists doesn't answer my question is because this is on duplicate key. However, id is a key and user_id is a key. One user_id may have multiple driver_id's. Using INSERT ON DUPLICATE doesn't let me pick a specific key to update on. What would I do?

James
  • 299
  • 1
  • 12
  • this might help https://stackoverflow.com/questions/33495194/mysql-insert-on-duplicate-update-for-non-primary-key – SABER Aug 07 '20 at 07:45
  • @SABER-FICTIONALCHARACTER Thats for one query. I want to bulk insert/update – James Aug 07 '20 at 08:09
  • As MySQL documentation says: "If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row occurs". So you can add UNIQUE index on any field or combination of fields and use `INSERT ON DUPLICATE` – Alexander Ushakov Aug 07 '20 at 12:56

0 Answers0