I have this customer
table:
id
= primary keydriver_id
= unique intuser_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?