Mastering the SQL UPDATE Statement: Updating tables and values

The SQL UPDATE statement is a powerful command that allows you to modify the existing data in a SQL Server table.
This post will cover everything you need to know about the SQL UPDATE statement, including its syntax, parameters, and examples.

SQL Server: UPDATE Statement

The SQL UPDATE statement is used to modify the existing data in a table. It allows you to change one or more columns in a table based on a condition or a set of conditions. We can use the UPDATE statement to modify a single row, multiple rows, or an entire table.

The Update command in SQL is used in combination with the SET and WHERE clauses. The SET clause is used to specify the new values for the columns being updated, and the WHERE clause is used to specify the condition determining which rows are being updated.

Syntax

The basic syntax of the SQL UPDATE statement is as follows:

UPDATE table_name 
SET column1 = value1, column2 = value2, ... 
WHERE condition;

Here, table_name is the name of the table that you want to update. The SET keyword indicates the new values for the columns you want to update. It is done by specifying a list of column-value pairs. Finally, you use the WHERE keyword to specify the condition that determines which rows should be updated.

We will use the following PRODUCTS and CUSTOMERS table for the demo purpose.

01. Customers Table:

02. Products Table:

Example 1: Update single column

To update a single column in a table, you can use the following syntax:

UPDATE table_name
SET column_name = new_value
WHERE condition;

For example, if you want to update the price of a product with an ID of 1001, you can use the following query:

UPDATE products
SET price = 15.99
WHERE product_id = 1001;

Output:

Example 2: Update multiple columns

To update multiple columns in a table, you can use the following syntax:

UPDATE table_name SET column1 = value1, column2 = value2  WHERE condition;

For example, if you want to update the price and quantity of a product with an ID of 1001, you can use the following query:

UPDATE products
SET price = 20.99, quantity = 20
WHERE product_id = 1001;

Let’s see the updated record.

Select * from products
WHERE product_id = 1001;

Output:

Example 3: Update table with data from another

You can use the SQL UPDATE statement to update a table with data from another table. The syntax for this is as follows:

UPDATE table_name
SET column1 = other_table.column1, column2 = other_table.column2, ...
FROM other_table
WHERE condition;

For example, if you want to update the price and quantity of a product with an ID of 1001 based on the values in another table called new_products, you can use the following query:

UPDATE products
SET price = new_products.price, quantity = new_products.quantity
FROM new_products
WHERE products.product_id = new_products.product_id
AND new_products.product_id = 1001;

Example 4: Update Table in SQL

To update an entire table in SQL, you can use the following syntax:

UPDATE table_name
SET column1 = value
WHERE condition;

For example, if you want to update the price of all products in the products table to 20.99, you can use the following query:

UPDATE products
SET price = 20.99;

Example 5: Update multiple rows in SQL Server

You can use the SQL UPDATE statement to update multiple rows in a table based on a condition.

For example, if you want to update the price of all products that are currently out of stock, you can use the following query:

UPDATE products
SET price = price * 1.1
WHERE quantity = 0;

This query will update the price of all products with a quantity of 0, increasing the price by 10%.

Let’s see the updated record.

SELECT * FROM products where quantity =0;

It is important to exercise caution when updating records in SQL. Inadvertently removing the WHERE clause from the UPDATE statement will result in updating all records in the table.
Hence, it is essential to double-check and ensure that the WHERE clause is included before executing the SQL UPDATE statement.

References-MSDN-UPDATE statement in SQL Server

FAQs

Conclusion:

The SQL UPDATE statement is used for modifying data in a SQL Server table. It allows you to change one or more columns in a table based on a condition or a set of conditions.
You can easily perform a wide range of data manipulation tasks using SQL Update statement. Whether you are a web developer, data analyst, or business professional, understanding the SQL UPDATE statement is essential for effective database management.

You might want to read this too:

• Having VS Where Clause in SQL
• SQL EXISTS – Exploring EXISTS Operator in SQL Server
• Like operator in SQL
• COALESCE in SQL With Examples and Use Cases for Handling NULL Values
• SQL Server CONVERT Function: How to Convert Data Types in SQL Server
• Delete vs Truncate vs Drop in SQL Server
• SQL Server TRY_CAST() Function – Understanding TRY_CAST() in SQL with [Examples]
• SQL Server Try Catch: Error Handling in SQL Server With [Examples]
• Triggers in SQL Server
• Different ways to delete duplicate rows in SQL Server
• Types of Joins in SQL Server
• Function vs Stored procedure in SQL Server
• UNION vs UNION ALL in SQL SERVER
• View In SQL | Types Of Views In SQL Server
• SQL pivot tables: Understanding pivot tables in SQL Server
• Group by Month in SQL: A Comprehensive Guide with Examples in SQL Server

Let others know about this post by sharing it and leaving your thoughts in the comments section.

• Author
• Recent Posts

Shekh Ali

Meet Sheikh Ali, a skilled Software engineer and passionate a blogger. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Sheikh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• Difference Between Array And ArrayList In C#: Choosing the Right Collection - May 28, 2024
• C# Program to Capitalize the First Character of Each Word in a String - February 21, 2024
• C# Program to Find the Longest Word in a String - February 19, 2024