DEV Community

Hòa Nguyễn Coder
Hòa Nguyễn Coder

Posted on

Using ON DUPLICATE KEY UPDATE and NOT EXISTS in MySQL

SQL Tip📢
Some sql query statements we often use

/* 
Insert data from table2 of database db2 into table1 of database db1.
 */

INSERT INTO db1.table1 (column1, column2, column3)
SELECT column1, column2, column3
FROM db2.table2;

/* 
If you need to filter data during insertion, use a WHERE condition. For example: Only insert rows where column4 = 'some_value'
 */
INSERT INTO db1.table1 (column1, column2, column3)
SELECT column1, column2, column3
FROM db2.table2
WHERE column4 = 'some_value';

/* 
Insert data from table2 into table1. If duplicate (based on PRIMARY KEY or UNIQUE KEY), perform an update (UPDATE)
 */
INSERT INTO db1.table1 (column1, column2, column3)
SELECT column1, column2, column3
FROM db2.table2
ON DUPLICATE KEY UPDATE
column2 = VALUES(column2),
column3 = VALUES(column3);

/* 
"Insert data from table2 into table1. Ensure that only non-duplicate rows are inserted (using the NOT EXISTS condition).
 */
INSERT INTO db1.table1 (column2, column3)
SELECT column2, column3
FROM db2.table2
WHERE NOT EXISTS (
    SELECT 1
    FROM db1.table1
    WHERE db1.table1.column2 = db2.table2.column2
      AND db1.table1.column3 = db2.table2.column3
);

/* 
Update the value of a column in a table, replacing a specific string. For example: Replace '100daysofcode.hoanguyenit.com' with 'hoanguyenit.com'.
 */
UPDATE your_table_name
SET your_column_name = REPLACE(your_column_name, '100daysofcode.hoanguyenit.com', 'hoanguyenit.com')
WHERE your_column_name LIKE '%100daysofcode.hoanguyenit.com%';

Enter fullscreen mode Exit fullscreen mode

TikTok :🖥️💻👨‍💻 Hòa Nguyễn Coder 👨‍💻💡

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more