Update Data in SQL Server

Update Data in SQL Server

Introduction

This documentation provides a comprehensive guide on updating data in SQL Server. The SQL UPDATE statement is used to modify existing records in a table. This document will cover the syntax of the UPDATE statement and provide coding examples along with explanations for better understanding.

Prerequisites

Before proceeding, ensure that you have the following:

• A working installation of SQL Server.
• Access to a database with appropriate privileges to perform updates.

Syntax of the UPDATE Statement

The basic syntax of the UPDATE statement is as follows:

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

Explanation:

• table_name: The name of the table to update.
• SET: Specifies the columns to be updated and their new values.
• column1 = value1, column2 = value2, ...: Pairs of columns and their corresponding values that need to be updated.
• WHERE: An optional clause to specify the condition to filter the rows that will be updated. If not provided, all rows in the table will be updated.

Examples

Let's demonstrate various scenarios of using the UPDATE statement:

Example 1: Updating a Single Column

sql
-- Update the 'price' column of the 'products' table for product ID 1001
UPDATE products
SET price = 19.99
WHERE product_id = 1001;

Explanation:
This query updates the 'price' column for the product with ID 1001, setting its new value to 19.99.

Example 2: Updating Multiple Columns

sql
-- Update the 'quantity' and 'modified_date' columns of the 'inventory' table for item ID 2002
UPDATE inventory
SET quantity = 50, modified_date = GETDATE()
WHERE item_id = 2002;

Explanation:
Here, we update both the 'quantity' and 'modified_date' columns of the 'inventory' table for the item with ID 2002. The GETDATE() function retrieves the current date and time, setting the 'modified_date' to the current timestamp.

Example 3: Updating All Rows

sql
-- Update the 'status' column of the 'orders' table, setting all orders to 'Completed'
UPDATE orders
SET status = 'Completed';

Explanation:
In this example, we update the 'status' column of the 'orders' table, setting all orders to 'Completed' without any condition. This will update the entire 'status' column for all rows in the table.

Example 4: Conditional Update

sql
-- Update the 'discount' column of the 'customers' table for customers with a total purchase greater than $500
UPDATE customers
SET discount = 10
WHERE total_purchase > 500;

Explanation:
This query updates the 'discount' column for customers in the 'customers' table who have a total purchase greater than $500. It sets their 'discount' value to 10.

Conclusion

Updating data in SQL Server is a powerful operation that allows you to modify existing records in a table based on specified conditions. Proper understanding of the UPDATE statement and its syntax will enable you to efficiently manage and maintain your database.