Using the UPSERT Statement

The word UPSERT combines UPDATE and INSERT, describing it statement's function. Use an UPSERT statement to insert a row where it does not exist, or to update the row with new values when it does.

For example, if you already inserted a new row as shown below, executing the subsequent UPSERT statement updates user John’s age to 27, and income to 60,000. If you did not execute the previous INSERT statement, the UPSERT statement inserts a new row with user id 10 to the Users table.

sql-> INSERT INTO Users VALUES (10, "John", "Smith", 22, 45000);
{"NumRowsInserted":1}
1 row returned
sql-> UPSERT INTO Users VALUES (10, "John", "Smith", 27, 60000);
{"NumRowsInserted":0}
1 row returned
sql-> UPSERT INTO Users VALUES (11, "Mary", "Brown", 28, 70000);
{"NumRowsInserted":0}
1 row returned

sql-> select * from Users;
{"id":10,"firstname":"John","lastname":"Smith","age":22,"income":60000}
{"id":11,"firstname":"Mary","lastname":"Brown","age":28,"income":70000}
2 rows returned