A relational database management system uses SQL MERGE (upsert) statements to INSERT new records or UPDATE existing records depending on whether or not a condition matches. It was officially introduced in the SQL:2003 standard.
Contents |
Usage
MERGE INTO table_name USING table_name ON (condition) WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...] WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])
Other non-standard implementations
Some other database management systems support this, or very similar behavior, through their own, non-standard SQL extensions.
MySQL, for example, supports the use of INSERT ... ON DUPLICATE KEY UPDATE syntax[1] which can be used to achieve the same effect. It also supports ...INSERT INTO syntax[2], which has a similar (although subtly different) effect.
SQLite's INSERT OR REPLACE INTO works similarly.
Firebird supports both MERGE INTO and a single-row version, UPDATE OR INSERT INTO tablename (columns) VALUES (values) [MATCHING (columns)], but the latter does not give you the option to take different actions on insert vs. update (e.g. setting a new sequence value only for new rows, not for existing ones.)
References
- ^ MySQL :: MySQL 5.1 Reference Manual :: 12.2.4.3 INSERT ... ON DUPLICATE KEY UPDATE Syntax
- ^ MySQL 5.1 Reference Manual: 11.2.6 REPLACE Syntax
External links
|
||||||||||||||||||||
 |
This database-related article is a stub. You can help Wikipedia by expanding it. |
This entry is from Wikipedia, the leading user-contributed encyclopedia. It may not have been reviewed by professional editors (see full disclaimer)
