How to use SQL UPDATE

SQL UPDATE can be used to update a table as a whole or to make changes to selected entries. It relieves you of the tedious work of updating entries line by line, making it one of the most important tools in SQL.

What is SQL UPDATE?

The quality of databases and efficiency of queries is closely related to how updated those databases are. Incorrect, incomplete or outdated records can not only skew analyses and reports but also lead to weak points in data protection and performance. That’s why SQL UPDATE is such an important SQL command – it allows you to update, edit and correct data in selected tables and records.

Using SQL UPDATE to keep your data up to date helps to avoid inconsistencies, reduce errors in databases and optimise your efficiency and performance. The command can also be used with different data types, with scripts for automation and with other SQL operators.

What is the syntax of SQL UPDATE?

The basic syntax of SQL UPDATE looks as follows:

UPDATE  Table
SET  ColumnA  =  New_value1,  ColumnB  =  New_value2
WHERE  Condition

The following parameters can be used with it:

• UPDATE: Specifies which table you want to make updates in.
• SET: Specifies which column you want to update and which values you want to update them with.
• WHERE: WHERE clauses allow you to define conditions that a column needs to meet to be updated. It is useful if you only want to update a subset of records.

What is SQL UPDATE used for?

SQL UPDATE is useful for a variety of cases in which you need to update data as simply as possible. It’s particularly indispensable for database maintenance in situations such as:

• Correcting errors, inaccuracies or inconsistencies
• Updating master data, product information, contact details and prices
• Adjusting inventories or order/shipping status
• Updating financial data after transactions
• Marking or labelling customer or product data

Examples for SQL UPDATE

We’ll now take a look at two easy examples that show how SQL UPDATE is used in practice.

Updating product data

In this example, we’ll work in a table called ‘Products’ and change ‘Makeup’ to ‘Hair & Makeup’ in the ‘Category’ column.

UPDATE  Products
SET  Category  =  'Hair & Makeup'
WHERE  Category  =  'Makeup'

Updating employee data

In this example we’ll change the name of the department called ‘Finance’ to ‘Distribution and finance’:

SELECT  Employees
SET  Department  =  'Distribution and finance'
WHERE  Category  =  'Finance'

What are the alternatives to SQL UPDATE?

In addition to UPDATE, there are a number of other SQL commands, statements and functions for changing and updating data:

• MERGE: Similar to UPDATE, MERGE can be used to change or adjust records in a target table based on a source table. Note that it’s not recommended to use MERGE if you are already using UPDATE, INSERT or DELETE, since MERGE essentially combines those functions. It can also recognise duplicates and can be combined with JOIN and subqueries.
• INSERT or INSERT INTO … SELECT: SQL INSERT INTO SELECT can be used to copy data from a source table into a target table or update records in a target table. You can use it to both merge data and change data in a target table. Similar to MERGE and in contrast to UPDATE, it does not require the use of a WHERE clause.
• DELETE: SQL DELETE allows you to delete a single record or several records.