[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.
- The transaction size should be kept as short as possible during an UPDATE operation.
- Do not update all the table rows at once. Always filter rows to update using the WHERE clause.
- Table relationships, e.g., foreign key constraints, must be considered while updating a table.
- Major updates should only be performed during low peak usage times.
- 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:
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:
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:
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:
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:
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:
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 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:
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.
