[Detailed Guide] SQL Update Statement

This article explains how to use the SQL UPDATE statement for updating table rows in SQL SERVER.

It looks at what the SQL UPDATE statement is, how you can use it, and some rules and best practices for using it.

Update statements are covered during our SQL training courses if you would like to learn more.

 

WHAT ARE SQL UPDATE STATEMENTS?

SQL UPDATE statements update an existing database table row.

You can update a single column value or multiple values in a row using the SQL UPDATE statement.

 

WHY USE SQL UPDATE STATEMENTS

SQL UPDATE statements are most useful when data keeps updating all the time, and you need a way to upload the changes to a database.

The UPDATE statement does this job for you. The UPDATE statements can also be used with the JOIN clause to update rows with values from joining columns.

 

HOW TO CREATE AN SQL UPDATE STATEMENT

The UPDATE and SET keywords are used to create an UPDATE statement in SQL Server. The syntax of the UPDATE statement is as follows:

UPDATE table_name

SET column_name = updated_value

 

Here is a more concrete example of creating an SQL UPDATE statement.

The script below updates all the rows of the Products table by assigning a new value for the Discount column.

 

UPDATE Products

SET Discount = 0.5

RULES & BEST PRACTICE FOR SQL UPDATE STATEMENT

Following are some rules and best practices that should be followed while executing an SQL UPDATE statement.

  1. The transaction size should be kept as short as possible during an UPDATE operation.
  2. Do not update all the table rows at once. Always filter rows to update using the WHERE clause.
  3. Table relationships, e.g., foreign key constraints, must be considered while updating a table.
  4. Major updates should only be performed during low peak usage times.
  5. Always backup your SQL database before executing an UPDATE statement.

 

A SIMPLE EXAMPLE OF SQL UPDATE STATEMENTS

Let’s look at an example of how to use SQL UPDATE statements.

The script below selects data from all the columns of the Order Details table of the Northwind database.

 

USE Northwind

SELECT * FROM [Order Details]

 

Output:

simple example of sql update statements. image 1

 

We will update the Order Details table by updating the value of the Discount column as shown below:

 

USE Northwind
UPDATE [Order Details]
SET Discount = 0.5

SELECT * FROM [Order Details]

 

Output:

simple example of sql update statements. image 2

 

Similarly, you can update multiple columns at once using the SQL UPDATE statement. The following script updates values of the Quantity and Discount columns of the Order Details table:

 

USE Northwind
UPDATE [Order Details]
SET Quantity = 10, Discount = 0.7

SELECT * FROM [Order Details]

 

Output:

 

simple example of sql update statements. image 3

 

MORE COMPLEX EXAMPLE OF SQL UPDATE STATEMENTS

Instead of updating values for all rows in a column, you can filter rows to update using the WHERE clause.

For instance, the script below updates the values in the Discount column only for the rows for which the UnitPrice column contains a value greater than 30.

 

USE Northwind

UPDATE [Order Details]
SET Discount = 0.9
WHERE UnitPrice > 30.0

SELECT * FROM [Order Details]

 

Output:

 

more complex example of sql update statements. image 1

 

Similarly, you can use the WHERE clause to filter rows to update for multiple columns in the UPDATE statement. Here is an example of how to do that:

USE Northwind

UPDATE [Order Details]
SET Discount = 0.2, Quantity = 50
WHERE UnitPrice < 10.0

SELECT * FROM [Order Details]

 

Output:

 

more complex example of sql update statements. image 2

 

USING THE UPDATE STATEMENT WITH SQL JOINS

You can also use the UPDATE statement in conjunction with the JOIN clause. Let’s see an example of how to do that.

The following script assigns a dummy value ‘XYZ’ to the ShipAddress column of the Orders table of the Northwind database.

 

USE Northwind
UPDATE Orders
SET ShipAddress = ‘XYZ
SELECT CustomerID, ShipAddress FROM Orders

 

Output:

 

using update statement with sql joins. image 1

 

We want to assign the values from the Address column of the Customer table to the ShipAddress column of the Orders table for the rows where the CustomerID column of the Orders table equals CustomerID column of the Customers table.

You can use an SQL UPDATE statement with an INNER JOIN clause. Here is an example.

 

USE Northwind

UPDATE Ord
SET Ord.ShipAddress = Cus.Address
FROM Orders Ord
INNER JOIN
Customers Cus
ON Ord.CustomerID = Cus.CustomerID

SELECT CustomerID, ShipAddress FROM Orders

 

Output:

 

using update statement with sql joins. image 2

 

USING SQL UPDATE STATEMENT IN A STORED PROCEDURE

Finally, you can use an SQL UPDATE statement inside a stored procedure, as you can a SQL IF statement.

For example, the following script creates a stored procedure spUpdatePrice which accepts one parameter @NewPrice.

The stored procedure updates the UnitPrice column of the Products table using the @NewPrice parameter value.

The stored procedure selects ProductName, and UnitPrice column values from the Products table.

 

USE Northwind

GO

CREATE PROC spUpdatePrice (@NewPrice FLOAT)
AS
BEGIN
             UPDATE Products
             SET UnitPrice = @NewPrice
             SELECT ProductName, UnitPrice FROM Products
             RETURN
END

 

The script below calls the spUpdatePrice stored procedure using a dummy value for the @NewPrice parameter. In the output, you can see updated values for the UnitPrice column of the Products table.

 

EXEC spUpdatePrice @NewPrice = 15.70

 

Output:

 

using sql update statement in a stored procedure. image 1

 

CONCLUSION

The SQL UPDATE statement is one of the most useful functions in SQL.

In this article, you saw how to update table rows using the SQL UPDATE statement. You also saw how to using the SQL UPDATE statement with a SQL JOIN clause and inside a stored procedure.

It is important to remember the difference between an UPDATE and ALTER statement.

While the UPDATE statement updates a table row, the ALTER statement is used to modify the table scheme, e.g., adding new rows to a table column, etc.

In order to keep expanding your SQL knowledge, we suggest using SQL learning apps, available for iPhone and Android. Learn the theory and practise anywhere, at any time.

About Ben Richardson

Ben is a director of Acuity Training. He writes about SQL, Power BI and Excel on a number of industry sites including SQLCentral, SQLshack and codingsight.