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%';

TikTok :?️??‍? Hòa Nguyễn Coder ?‍??

The above is the detailed content of Using ON DUPLICATE KEY UPDATE and NOT EXISTS in MySQL. For more information, please follow other related articles on the PHP Chinese website!