<p dir="auto"><strong>Migrated issue, originally created by Михаил Доронин (<a href="https://github.com/warrior2031">@warrior2031</a>)</strong></p>
<p dir="auto">I would like to provide cross-platform Merge statement. I've seen this <a href="https://bitbucket.org/zzzeek/sqlalchemy/issues/960/implement-merge-and-or-pg-on-conflict-and" rel="nofollow">issue</a></p>
<p dir="auto">I think there should be both dialect specific variants as well as the lowest common denominator between them. The reasons are</p>
<ul dir="auto">
<li>It's in sql 2003 standard and many databases support actual standard (okay, maybe they not so popular for sqlalchemy users)</li>
<li>It is now possible to implement lowest common denominator implementation in mysql, sqlite  (maybe not that performant, but who uses sqlite for performance? And user can be warned about that in docs) and postgresql.</li>
<li>I believe that lowest common denominator is the actual most useful (and used) use case. Which is "insert values or update existing ones". Kind of like <code class="notranslate">{**d1, **d2}</code> in python3.</li>
</ul>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="/* postgresql */
INSERT INTO table (a, b)
    VALUES (1, 2), (2, 3)
    ON CONFLICT (a) DO UPDATE SET dname = EXCLUDED.dname;

/* mysql */
INSERT INTO table (a, b)
    VALUES (1, 2), (2, 3)
    ON DUPLICATE KEY a=VALUES(a) UPDATE

/* sqlite */
INSERT OR REPLACE INTO table (a, b) 
    VALUES
    (1, COALESCE((SELECT role FROM Employee WHERE id = 1), 2)),
    (2, COALESCE((SELECT role FROM Employee WHERE id = 2), 3))


/* merge from wikipedia */
MERGE INTO table USING table_reference ON (condition)
   WHEN MATCHED THEN
   UPDATE SET column1 = value1 [, column2 = value2 ...]
   WHEN NOT MATCHED THEN
   INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]);"><pre class="notranslate"><code class="notranslate">/* postgresql */
INSERT INTO table (a, b)
    VALUES (1, 2), (2, 3)
    ON CONFLICT (a) DO UPDATE SET dname = EXCLUDED.dname;

/* mysql */
INSERT INTO table (a, b)
    VALUES (1, 2), (2, 3)
    ON DUPLICATE KEY a=VALUES(a) UPDATE

/* sqlite */
INSERT OR REPLACE INTO table (a, b) 
    VALUES
    (1, COALESCE((SELECT role FROM Employee WHERE id = 1), 2)),
    (2, COALESCE((SELECT role FROM Employee WHERE id = 2), 3))


/* merge from wikipedia */
MERGE INTO table USING table_reference ON (condition)
   WHEN MATCHED THEN
   UPDATE SET column1 = value1 [, column2 = value2 ...]
   WHEN NOT MATCHED THEN
   INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]);
</code></pre></div>
<p dir="auto">possible sqlalchemy interface <code class="notranslate">insert(table, [(1, 2), (2, 3)]).merge(table.c.a)</code></p>
<p dir="auto">I would be very happy to provide implementation if you can provide guidance on how to do that. I'm trying to understand your code, but aside from understanding that there is <code class="notranslate">Insert</code> class in <code class="notranslate">sql/dlm.py</code> and there are probably implementations of it in all dialects I haven't been able to figure out much.</p>
<p dir="auto">Maybe you can show how to make that for postgres since it already has <code class="notranslate">on_conflict</code> in dialect. And then I can use that as example to add this to sqlite, sqlalchemy and maybe other dialects.</p>
<p dir="auto"><strong>Migrated issue, originally created by jek (<a href="https://github.com/jek">@jek</a>)</strong></p>
<p dir="auto">Implement generic MERGE, aka 'upsert'.  In ANSI, it looks like:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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) "><pre class="notranslate"><code class="notranslate">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) 
</code></pre></div>
<p dir="auto">Dialect support differs pretty widely.  A quick &amp; likely inaccurate poll:</p>
<ul dir="auto">
<li>Oracle 9+ has a MERGE</li>
<li>t-sql 2008 has a MERGE, earlier can maybe do <code class="notranslate">IF EXISTS(SELECT ...)</code></li>
<li>MySQL is limited to a key violation condition, and can do either <code class="notranslate">INSERT ... ON DUPLICATE KEY UPDATE</code> or <code class="notranslate">REPLACE INTO</code>, INSERT being preferable</li>
<li>SQLite is limited to a key violation condition, and has <code class="notranslate">INSERT .. ON CONFLICT REPLACE</code></li>
</ul>