Utilisation de ON DUPLICATE KEY UPDATE et NOT EXISTS dans MySQL

Astuce SQL ?
Quelques instructions de requête SQL que nous utilisons souvent

/* 
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 ?‍??

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!