sqlalchemy: cross-platform MERGE that leverages existing ON CONFLICT, etc. clauses within dialects

Migrated issue, originally created by jek (@jek)

Implement generic MERGE, aka ‘upsert’. In ANSI, it looks like:

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) 

Dialect support differs pretty widely. A quick & likely inaccurate poll:

  • Oracle 9+ has a MERGE
  • t-sql 2008 has a MERGE, earlier can maybe do IF EXISTS(SELECT ...)
  • MySQL is limited to a key violation condition, and can do either INSERT ... ON DUPLICATE KEY UPDATE or REPLACE INTO, INSERT being preferable
  • SQLite is limited to a key violation condition, and has INSERT .. ON CONFLICT REPLACE

About this issue

  • Original URL
  • State: closed
  • Created 16 years ago
  • Comments: 40 (4 by maintainers)

Most upvoted comments