Consolidating data with MERGE

The MERGE command is a SQL standard way to insert new data into a table, even when new data partially overlaps with the existing data.

We will illustrate MERGE using the example of a shop manager who records the amount of each article in a database table. The manager will use the table to know which articles are running out and to order the correct amount of articles at the right time.

Getting ready

Create the initial table with some plausible data, representing the current inventory of the shop:

CREATE TABLE articles
( id int PRIMARY KEY
, description text NOT NULL
, quantity int NOT NULL
);
INSERT INTO articles (id, quantity, description) VALUES
  (1, 15, 'Aubergines')
, (2,  6, 'Bananas')
, (3, 34, 'Carrots')
, (4, 22, 'Dates (kg)')
;

Now, we create a temporary table representing the list of supplies that have been ordered:

CREATE TEMP TABLE new_articles (LIKE articles);
INSERT INTO new_articles...