SQL UPDATE vs. INSERT vs. UPSERT

In any application, you need to add records to database tables and change information they store. The UPDATE SQL statement changes data already stored in the database, and the INSERT statement adds a new record to a table. The UPSERT statement is a combination of INSERT and UPDATE and performs an update or adds a record if it doesn’t already exist. Here, we are going to discuss SQL UPDATE vs. INSERT vs. UPSERT.

How to Use UPDATE in SQL: Examples

The UPDATE statement updates a current record with new information that you specify. For example, a customer might move and change their contact information in an e-commerce table, and an UPDATE statement will perform the change. Any changes you make in a database will use the UPDATE statement.

It’s important to note that optimizing queries reduces database latency. An optimized UPDATE query can reduce data processing to mere milliseconds. Infrastructure is also important for low latency on high-volume database servers. Use the following queries to get started. Indexing columns commonly updated could help improve performance. On large concurrent workloads, it might be necessary for database developers to use isolation levels to shield statements from affecting other statements also processing.

As an aside, developers and database administrators should have error handling in their code. These examples show how to perform each command, but your business logic should determine what to do if an error triggers. For example, if the database fails to insert a new customer record, notify the user to try again or contact customer service. Database latency, errors, and optimization warnings can be accomplished with performance monitoring tools.

UPDATE in SQL: Examples

For most SQL UPDATE statements, you use the WHERE clause. The WHERE clause determines which records will be updated and leaves the others untouched. If you don’t use a WHERE clause when executing an UPDATE statement, the database will make changes to all records in the database, which is likely an error. Make sure you have a WHERE clause in your SQL statement to avoid this mistake.

The UPDATE statement syntax depends on the database engine that you use. The MySQL and Oracle SQL databases use similar syntax for their UPDATE statements. The following examples change a customer’s first name to “john” where the customer’s ID is 44.

MySQL: UPDATE Command

UPDATE Customer

SET first_name = ‘John”

WHERE id = 44;

Oracle SQL: UPDATE Command

UPDATE Customer

SET first_name = ‘John”

WHERE id = 44;

How to Use INSERT in SQL: Examples

When you want to add a new record to a table, you use the INSERT SQL statement. Most database designers create a default column to give every record a unique identifier. The unique identifier automatically populates, so you do not insert it manually and allow the database engine to do it for you. You cannot insert duplicate values into identifier rows, so it’s more efficient to allow the database to do it for you.

In the following examples, a new customer is added to the Customer table with a first name and last name populated in the appropriate columns. Notice that the columns are defined along with the values to add to each column respectively.

MySQL: UPDATE Command

INSERT INTO Customer (first_name, last_name)

VALUES (‘John’, ‘Smith’)
Oracle SQL: UPDATE Command
INSERT INTO Customer (first_name, last_name)

VALUES (‘John’, ‘Smith’)

How to Use UPSERT in SQL: Examples

The UPSERT command is a useful statement when you want to insert a row only if it doesn’t already exist. If it does already exist, then the database will update the record instead with new information. The Oracle database engine has a specific UPSERT command. With MySQL, the command uses the term REPLACE instead.

Application developers use the UPSERT command to avoid duplicate records especially when they have unique identifiers tied to them, and application statements should not insert a record due to a previously existing record. It often happens when you import data from one table to another, and it’s possible that duplicate records exist. For example, you get a list of customer records from an acquired business and know that some customers are already in the current business database. The UPSERT statement will only update existing customers and add new ones where they don’t exist.

MySQL: UPDATE Command


REPLACE INTO Customer (id, first_name, last_name)

VALUES (44, ‘John’, ‘Smith’)

In the example above, if the customer with an identifier of 44 already exists, the original record is deleted and then a new customer record with an identifier of 44 is added. It’s important to remember that the REPLACE command doesn’t technically update any data. Instead, it deletes and re-adds a row to the table.

Oracle SQL: UPDATE Command

UPSERT INTO Customer (id, first_name, last_name)

VALUES (44, ‘John’, ‘Smith’)

With Oracle, the UPSERT command will update the customer with an identifier of 44 rather than delete and re-add it. If the record does not exist, then the Oracle engine will add the customer record to the table.

PostgreSQL: INSERT Command

PostgreSQL does not have a specific UPSERT statement, but it supports a type of INSERT where the server will only insert a new record if it does not already exist. If the record exists, table constraints should throw an error and perform an UPDATE rather than an INSERT statement. The PostgreSQL syntax is below:

SQL Server: MERGE Command

Microsoft SQL Server also doesn’t support an UPSERT statement, but developers can use the MERGE command to perform a similar action. The MERGE statement takes a source dataset and merges it with a table, but only inserts a new record if a row does not exist. If a row exist, the MERGE statement will update the record. 

MERGE products P

USING new_products_source S ON P.ID=S.ID

WHEN MATCHED THEN

UPDATE SET price=S.price, name=S.name, quantity=S.quantity;

MongoDB:  UPDATE Command

MongoDB has an UPDATE command parameter to tell the database to perform an UPSERT statement. Like the other databases, MongoDB will insert a new record only if the record does not exist. If it does exist, the database will perform and update on a record.

db.products.update(

{ title: “Red Widget” },

{ $inc: { price: 19.00 } },

{ upsert: true }

)

UPDATE vs. INSERT: Differences in SQL

Most applications use several UPDATE and INSERT statements to manage data. INSERT statements are necessary when you need to add a record. When you need to change data already stored, the UPDATE statement is necessary.

The INSERT statement requires all columns that must be filled with the necessary values. The UPDATE statement doesn’t require the WHERE clause, but if you exclude it, all records are updated with the given data. It’s unlikely that you want to update all records, so make sure that you include the WHERE clause.

You can’t use UPDATE instead of INSERT, but the UPSERT statement is a good alternative. With the UPSERT statement, the database updates records where they are available and inserts them in the table if the record does not exist. INSERT is faster than an UPDATE statement provided a database is properly optimized.

UPSERT vs. UPDATE and INSERT: Which Is Better?

The statement that you use depends on what you want to do with the data in your database. For performance, INSERT is better than UPSERT, but you can’t always avoid using an UPDATE statement and need a way to verify that the record doesn’t already exist. The UPSERT statement simplifies the entire process instead of writing a longer IF statement in a stored procedure. When you have large updates or inserts statements to import data and don’t want duplicates, the UPSERT statement is better for performance. You complete both UPDATE and INSERT in one action.

When to Use UPDATE in SQL

The only way to change data in a database table is with the UPDATE statement. Applications constantly change data for various reasons, and they use SQL UPDATE statements in the backend. When you change your contact information, credit card data, or a shipping address for an account online, it’s likely that the backend application system runs an UPDATE statement.

Use UPDATE when you only need to edit data and duplicates are not a concern. A simple UPDATE statement is not slow, but the more columns and rows that need to be edited can slow down database services. It might be beneficial to perform large batch UPDATE statement on off-peak hours.

When to Use INSERT in SQL

You need the INSERT statement when you want to add a record to a table. Whenever you sign up on an application or order a new product from an e-commerce store, the backend database uses the INSERT SQL statement to add a new record to the database. The new record can be changed later with the UPDATE statement, but it’s first created with the INSERT statement.

INSERT statements are generally fast. When you need to add a record, it is unavoidable. Like UPDATE statements, large inserts (e.g., like importing data from a static file) might be better optimized if they run during off-peak hours. Log files for auditing and logging use simple INSERT statements.

When to Use UPSERT in SQL

The UPSERT statement has the least performance out of all three of the commands, but it has a purpose when you have several records to add to a table and don’t want to duplicate them. When you import data into a table and think there could be duplicate records, use the UPSERT statement to update current records instead of adding duplicate ones. For example, if you need to import customers from an external file to your database tables, an UPSERT statement might be more appropriate than using an INSERT statement.

UPSERT statements are generally beneficial if you have a large source dataset that could have duplicates. For example, you wouldn’t want to duplicate a product table, so you would use UPSERT instead of using one INSERT statement and removing duplicates later. UPSERT is great when you have large batches of data but could cause latency issues if they are used for all INSERT statements unnecessarily. Only use UPSERT when you could be importing duplicates to a main table.