MySQL Upsert – UPDATE If Row Exist With Multiple Columns As Parameter

Posted on by

Like the other RDBMS out there, MySQL has a feature such as Upsert Statement. Upsert Statement means it’s query will update the row if data / row that match with our key or parameters (contraint, foreign key, or some columns we use as key in where clause) already exist. But if data / row doesn’t exist before, it will insert as a new row in our table.

Here some ways you can use upsert statement in MySQL:

  1. Use REPLACE statement
    It will update if there is same data/row with primary keys as paramater.
  2. Use INSERT IGNORE statement
    It will ignore existing data.
  3. Use INSERT ON DUPLICATE KEY statement
    It will update the row if there is same data with contraint or key as paramater.
  4. Use my Custom SQL below!
    If your table there is no contraint or primary key in the properties table, use this.

You can find many references about poin 1 to 3 on the internet, I will give the details about poin 4 above.

So if your table doesn’t have a proper constraint or key, but you know wich column that you used for parameter or key to filter the data or row. If you still doesn’t understand yet about what I said, just look on these sample structure below.

CREATE TABLE `sub_department` (
  `sub_department_id` varchar(15) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `department_id` varchar(15) DEFAULT NULL,
  `office_id` varchar(5) DEFAULT NULL
);
-- There is no PRIMARY KEY or CONTRAINT

And here is the sample data:

sub_department_idnamedepartment_idoffice_id
P011TEST DUMMY 1P01P0
P012TEST DUMMY 2P01P0

With those data, I want to run batch query for insert if row does not exist and update if row already exist with sub_department_id, department_id and office_id as my key. So how can I reach my goal? here is the query I used:

-- Row affected for insert
INSERT INTO sub_department SELECT 'B045','TEST DUMMY 3','B04','B0' FROM DUAL WHERE NOT EXISTS (SELECT * FROM sub_department WHERE sub_department_id='B045' AND name='TEST DUMMY 3' AND department_id='B04' AND office_id='B0') LIMIT 1;

-- Row affected for update
INSERT INTO sub_department SELECT 'P011','TEST UPDATE DUMMY 1','P01','P0' FROM DUAL WHERE NOT EXISTS (SELECT * FROM sub_department WHERE sub_department_id='P011' AND name='TEST DUMMY 1' AND department_id='P01' AND office_id='P0') LIMIT 1;

That’s it, for more information you can find on the MySQL Official Documentation, hope that help you guys.

Leave a Reply