How to insert if its a new data or update if it already exists

Question

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

create a unique index on driver_id or change the index to unique if there is one — Bernd Buffen, Jul 12 '20 at 06:09
Incidentally, the number in parentheses following an int declaration is almost meaningless — Strawberry, Jul 12 '20 at 09:01
@Strawberry Really? So I shouldn't put it? I'm just trying to save space and I know for a fact that I will never exceed `9999` — Jackie, Jul 13 '20 at 01:42
Yep, I'm not making it up. In that instance you would want SMALLINT. Although be aware that 'space' is very cheap. — Strawberry, Jul 13 '20 at 05:37

score 1 · Answer 1 · answered Jul 12 '20 at 06:54

Here is your Sample:

Create the Table

MariaDB [bernd]> CREATE TABLE customers (
                   cus_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                   driver_id INT(11),
                   name VARCHAR(32),
                   age int(11)
                );
                Query OK, 0 rows affected (0.01 sec)

Now insert the Data

    MariaDB [bernd]> INSERT INTO customers (driver_id,name,age) VALUES
     (1234,'Bob',20),
     (987,'James',21),
     (5000,'Jane',23);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0

Watch the Data

MariaDB [bernd]> SELECT * FROM customers;
+--------+-----------+-------+------+
| cus_id | driver_id | name  | age  |
+--------+-----------+-------+------+
|      1 |      1234 | Bob   |   20 |
|      2 |       987 | James |   21 |
|      3 |      5000 | Jane  |   23 |
+--------+-----------+-------+------+
3 rows in set (0.00 sec)

ADD the unique Key

MariaDB [bernd]> ALTER TABLE customers ADD UNIQUE (driver_id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

You can also add the Key directly with the CREATE TABLE Statement

Insert a Record with a duplicate driver_id

MariaDB [bernd]> INSERT INTO customers (driver_id, name, age) VALUES(1234, "Bobby", 21) ON DUPLICATE KEY UPDATE
    -> name="Bobby", age=21;
Query OK, 2 rows affected (0.01 sec)

Watch the changed Data

MariaDB [bernd]> SELECT * FROM customers;
+--------+-----------+-------+------+
| cus_id | driver_id | name  | age  |
+--------+-----------+-------+------+
|      1 |      1234 | Bobby |   21 |
|      2 |       987 | James |   21 |
|      3 |      5000 | Jane  |   23 |
+--------+-----------+-------+------+
3 rows in set (0.00 sec)

As @GordonLinoff mentioned, MySQL doesn't let you choose which key. So what is my solution to this problem? Because there is a very strong possibility that someone's `cus_id` and someones `driver_id` might be the same. So what is my solution. My main aim is to ensure that I don't have insert a duplicate person into my table and update a person if they already exist — Jackie, Jul 13 '20 at 01:41

score 0 · Answer 2 · answered Jul 12 '20 at 12:26

The issue is now, what is DUPLICATE KEY?

Your table definition has only one key, cus_id. And it is auto-incremented, so it can never be duplicated -- after all, the insert statement should be letting the database choose the next value.

If you want driver_id to be unique, you need a unique constraint or index on that column. The easiest method is when you create the table:

CREATE TABLE customers (
    cus_id int(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    driver_id int(4) NOT NULL UNIQUE
    name varchar(32),
    age int(3)
);

However, you can also add it after the fact as a unique constraint or index:

alter table customers add constraint unq_customers_driver_id unique(driver_id);

Or alternatively:

create unique index unq_customers_driver_id on customers(driver_id);

Unfortunately, MySQL does not let you "choose" which duplicated key gets processed. Any duplicated key triggers the ON DUPLICATE KEY UPDATE logic.

Well there is a very strong possibility that someone's `cus_id` and someones `driver_id` might be the same. So what is my solution. My main aim is to ensure that I don't have insert a duplicate person into my table and update a person if they already exist — Jackie, Jul 13 '20 at 01:40

How to insert if its a new data or update if it already exists

2 Answers2