SQL 方言參考/編寫查詢/替換查詢
< SQL 方言參考
替換查詢在沒有具有此主鍵的行時插入新行，或者如果存在則更新現有行。SQL:2003 標準引入了 MERGE 語句來實現此功能，而其他實現提供了名為“REPLACE”的類似查詢或所謂的“Upsert”查詢（UPDATE 和 INSERT 的混合詞）。
| 標準 | MERGE 語句可用於執行替換查詢MERGE INTO table_name1 USING table_name2 ON (condition)
WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN INSERT columns VALUES (values)
請注意，MERGE 的功能遠不止執行替換查詢。 |
|---|---|
| DB2 | MERGE 語句MERGE INTO phonebook AS p
USING ( VALUES ('john doe', '1234' ) ) AS v(name, extension)
ON ( p.name = v.name )
WHEN MATCHED
UPDATE SET p.extension = v.extension
WHEN NOT MATCHED
INSERT VALUES ( v.name, v.extension )
|
| Firebird | MERGE 語句MERGE INTO phonebook B
USING (
SELECT name
FROM phonebook
WHERE name = 'john doe') E
ON (B.name = E.name)
WHEN MATCHED THEN
UPDATE SET B.extension = '1234'
WHEN NOT MATCHED THEN
INSERT (name, extension)
VALUES ('john doe', '1234);
非標準簡化形式 UPDATE OR INSERT INTO phonebook (name, extension)
VALUES ('john doe', '1234')
MATCHING (name)
|
| Ingres | ? |
| Linter | ? |
| MonetDB | ? |
| MSSQL | MERGE 語句（從版本SQL Server 2008開始）DECLARE @UnitMeasureCode nchar(3) = 'ABC'
DECLARE @Name varchar(25) = 'Test name'
MERGE INTO Production.UnitMeasure AS target
USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
ON (target.UnitMeasureCode = source.UnitMeasureCode)
WHEN MATCHED THEN
UPDATE SET Name = source.Name
WHEN NOT MATCHED THEN
INSERT (UnitMeasureCode, Name)
VALUES (source.UnitMeasureCode, source.Name)
|
| MySQL | 允許 3 種語法：非標準 REPLACE 查詢、（從 4.1 開始）INSERT ... ON DUPLICATE KEY UPDATE 以及 IF EXISTS 的變體。REPLACE [INTO] table [(columns)] VALUES (values) INSERT INTO table (columns) VALUES (values) ON DUPLICATE KEY UPDATE column1=value1, column2=value2 IF EXISTS( SELECT * FROM phonebook WHERE name = 'john doe' )
THEN UPDATE phonebook SET extension = '1234' WHERE name = 'john doe'
ELSE INSERT INTO phonebook VALUES( 'john doe','1234' )
END IF
|
| Oracle | MERGE 語句MERGE INTO phonebook B
USING (
SELECT name_id
FROM phonebook
WHERE name = 'john doe') E
ON (B.name = E.name)
WHEN MATCHED THEN
UPDATE SET B.extension = '1234'
WHEN NOT MATCHED THEN
INSERT (B.name, B.extension)
VALUES ('john doe', '1234);
IF EXISTS( SELECT * FROM phonebook WHERE name = 'john doe' )
UPDATE phonebook SET extension = '1234' WHERE name = 'john doe'
ELSE
INSERT INTO phonebook VALUES( 'john doe','1234' )
|
| PostgreSQL | 從版本 9.5 開始，INSERT INTO...ON CONFLICT... 語法受 MySQL 啟發INSERT INTO distributors (did, dname)
VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;
|
| SQLite | REPLACE 語句REPLACE [INTO] table [(columns)] VALUES (values) （始終刪除舊行） |
| Virtuoso | ? |