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.
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.
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 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.
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.
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.
