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 update statement
SQL UPDATE Statement

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:

customer_idfirst_namelast_nameemail
1ShekhAlishekh.ali@example.com
2MikeDoemike.doe@example.com
3RohitKumarrohit.kumar@example.com
4AliceJohnsonalice.johnson@example.com
5TomJonestom.jones@example.com

02. Products Table:

product_idproduct_namepricequantity
1001Product A9.99100
1002Product B14.9950
1003Product C19.990
1004Product D24.9975
1005Product E29.9925

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:

sql-update-statement-example

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:

product_idproduct_namepricequantity
1001Product A20.9920

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;
product_idproduct_namepricequantity
1003Product C21.990

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

Q: Can the SQL UPDATE statement be used to delete rows from a table?

No, the SQL UPDATE statement is used to modify the existing data in a table. You would use the SQL DELETE statement to delete rows from a table.

Q: Is updating data in multiple tables with a single SQL UPDATE statement possible?

No, We can only use the SQL UPDATE statement to update data in a single table at a time. To update data in multiple tables, you need to use multiple SQL UPDATE statements or join the tables together using a SQL JOIN statement.

Q: Can the SQL UPDATE statement be used to insert new rows into a table?

No, the SQL UPDATE statement is used to modify existing data in a table. You would use the SQL INSERT INTO statement to insert new rows into a table.

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:

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

Shekh Ali
4.5 2 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments