mtFpvLvs

1MERGE INTO tablename USING table_reference ON (condition)
2   WHEN MATCHED THEN
3   UPDATE SET column1 = value1 [, column2 = value2 ...]
4   WHEN NOT MATCHED THEN
5   INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]);
6	
7DROP TABLE IF EXISTS distributors;
8DROP TABLE IF EXISTS distributors_upd;
9
10CREATE TABLE distributors (did INTEGER PRIMARY KEY, dname VARCHAR);
11INSERT INTO distributors
12VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc');
13
14CREATE TABLE distributors_upd (did INTEGER PRIMARY KEY, dname VARCHAR);
15INSERT INTO distributors_upd
16VALUES (5, 'Gizmo Transglobal*');
17	
18WITH distributors_updated AS (
19INSERT INTO distributors (did, dname)
20    (TABLE distributors_upd)
21    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname
22    RETURNING *
23)
24--SELECT * FROM distributors;
25SELECT * FROM distributors_updated;
26	
27did |           dname           
28-----+---------------------------
29   6 | Associated Computing, Inc
30   5 | Gizmo Transglobal*
31	
32-----+---------------------------
33   5 | Gizmo Transglobal
34   6 | Associated Computing, Inc
35(2 rows)
36	
37-----+--------------------
38   5 | Gizmo Transglobal*
39(1 row)