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:
- Use REPLACE statement
It will update if there is same data/row with primary keys as paramater.
- Use INSERT IGNORE statement
It will ignore existing data.
- Use INSERT ON DUPLICATE KEY statement
It will update the row if there is same data with contraint or key as paramater.
- 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:
|P011||TEST DUMMY 1||P01||P0|
|P012||TEST DUMMY 2||P01||P0|
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.