Menggunakan ON DUPLICATE KEY UPDATE dan NOT EXISTS dalam MySQL

Petua SQL?
Beberapa pernyataan pertanyaan sql yang sering kami gunakan

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

Atas ialah kandungan terperinci Menggunakan ON DUPLICATE KEY UPDATE dan NOT EXISTS dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!