I was looking at this question/answer here: Insert into a MySQL table or update if exists but I am confused. I have this table (customers):
cus_id | driver_id | name | age
1 | 1234 | Bob | 20
2 | 987 | James | 21
3 | 5000 | Jane | 23
SQL
CREATE TABLE customers(
cus_id int(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
driver_id int(4) NOT NULL,
name varchar(32),
age int(3)
);
I always search via driver_id
. cus_id
is the primary key and AUTO_INCREMENT
's. And always update name
and age
.
So I would use the statement
INSERT INTO customers (driver_id, name, age) VALUES(1234, "Bobby", 21) ON DUPLICATE KEY UPDATE
name="Bobby", age=21
The issue is now, what is DUPLICATE KEY? I am not searching the duplicate via the cus_id
. I and searching the duplicate my their driver_id
.
So in the above statement, it would update the row with the cus_id=1
, not insert a row because the driver_id
1234
already exists.
So the table would look like:
cus_id | driver_id | name | age
1 | 1234 | Bobby | 21
2 | 987 | James | 21
3 | 5000 | Jane | 23