Microsoft SQL Server

Chapter 6 - DML (Data Manipulation Language)

UPDATE

In Microsoft SQL Server, the Data Manipulation Language (DML) is used to modify data in tables. One of the most common DML operations is updating records in a table. The UPDATE statement allows you to modify existing records based on a condition or for all records in a table. This is particularly useful in scenarios like updating the price of products, modifying customer information, or adjusting sales data.

Below is a step-by-step explanation of how to use the UPDATE statement, along with examples to help you understand its use.

1. Basic Syntax of UPDATE Statement

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

table_name: The table where you want to update data.
SET: Specifies the column(s) and the new value(s).
WHERE: Limits the update to specific rows. If omitted, all rows will be updated.

2. Example: Updating a Single Column in a Table

Suppose we have a Products table, and we want to update the price of a product with ID 101.

UPDATE Products
SET Price = 29.99
WHERE ProductID = 101;

This will update the Price column for the product with ProductID = 101.

3. Example: Updating Multiple Columns

You can update multiple columns at once. Let's say we want to update both the Price and StockQuantity of a product.

UPDATE Products
SET Price = 19.99, StockQuantity = 150
WHERE ProductID = 102;

This will change both the price and stock quantity for the product with ProductID = 102.

4. Updating Multiple Rows

You can also update multiple rows by using a condition that affects more than one row. For example, updating the price of all products in a certain category:

UPDATE Products
SET Price = Price * 1.1
WHERE Category = 'Electronics';

This increases the price of all products in the 'Electronics' category by 10%.

5. Using UPDATE with INNER JOIN

Sometimes, you may need to update a table using information from another table. For example, if you want to update the SalesPrice in the Sales table based on a new price in the Products table:

UPDATE Sales
SET Sales.SalesPrice = Products.Price
FROM Sales
INNER JOIN Products ON Sales.ProductID = Products.ProductID
WHERE Sales.SaleDate = '2023-09-15';

This updates the SalesPrice in the Sales table to reflect the latest price from the Products table for sales made on September 15, 2023.

6. Best Practices

Always Use a WHERE Clause: To avoid updating all rows unintentionally, always use a WHERE clause unless you intend to update every row in the table.
Backup Your Data: Before performing large updates, especially in production environments, it’s a good idea to back up your data.
Use Transactions: If you're making multiple changes or updates, use transactions to ensure that all updates are committed only if all succeed. For example:

BEGIN TRANSACTION;

UPDATE Products
SET Price = 15.99
WHERE ProductID = 103;

UPDATE Sales
SET SalesPrice = 15.99
WHERE ProductID = 103;

COMMIT;

This ensures that either both updates happen, or none at all.

Test Updates: Always test your UPDATE statements on a smaller data set or in a staging environment before running them in production.

By following these best practices and using SQL Server's UPDATE statement effectively, you can safely modify data in your tables while minimizing the risk of errors.

Tansy SQL Course - UPDATE - Video Thumbnail

Previous Next

Comments(0 comments)

Comments Not Found