CRUD Statements  «Prev  Next»
Lesson 3 Overview of updating data
Objective Describe how to update data with Transact-SQL.

Updating Data in SQL-Server

Updating data in SQL Server 2019 is a critical task, and Transact-SQL (T-SQL) provides a powerful and flexible set of commands to carry out this operation. The `UPDATE` statement is used to modify existing data in a table, and it can be precisely configured to update specific columns based on certain conditions.

Syntax for Updating Specific Columns

UPDATE [table_name]
SET [column_name1] = [value1], [column_name2] = [value2], ...
WHERE [condition];
  • table_name: The name of the table containing the data you want to update.
  • column_name1, column_name2, ...: The names of the columns that you want to update.
  • value1, value2, ...: The new values that you want to set for the specified columns.
  • condition: A condition that specifies which rows should be updated. Only the rows that satisfy this condition will be updated.

Steps to Update Specific Columns in SQL Server 2019

  1. Connect to the Database: Use SQL Server Management Studio (SSMS) or any other preferred SQL client to connect to your SQL Server instance.
  2. Open a New Query Window: Once connected, open a new query window to write your T-SQL commands.
  3. Write the UPDATE Statement: In the query window, write your `UPDATE` statement, specifying the table name, the columns you want to update, their new values, and the condition to identify which rows should be updated.
  4. Execute the Statement: After writing the `UPDATE` statement, execute it. Make sure to review your statement carefully before executing to avoid updating unintended rows.
  5. Verify the Update: After executing the `UPDATE` statement, it is a good practice to verify that the data has been updated correctly. You can do this by executing a `SELECT` statement to retrieve the updated rows and inspect their values.

Example: Suppose you have a table named `Employees` with columns `EmployeeID`, `FirstName`, `LastName`, and `Salary`. To update the `Salary` of an employee with `EmployeeID` 101, you can use the following T-SQL command: 
UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 101;

This statement updates the `Salary` column to 60000 for the employee with `EmployeeID` 101.
Updating specific columns in a table is a common and crucial operation in SQL Server. The `UPDATE` statement in Transact-SQL provides a robust way to perform these updates, ensuring that you can modify your data accurately and efficiently. Always ensure to include a specific `WHERE` clause to prevent unintentional updates to other rows in the table. Additionally, it is highly recommended to back up your data or test your update commands on a staging environment before executing them on a production database to ensure data integrity and consistency.
To update data in a table, you use the UPDATE Transact-SQL statement. When updating data, you must specify the names of the specific columns that you are going to update.
The UPDATE statement uses the following general syntax:

Update Statement 1
1) Update Statement 1 
UPDATE{ 
table_name WITH(<table_hint > [...n])
| view_name
| rowset_function
}
Update Statement 2
2) Update Statement 2
Update Statement 3
3) Update Statement 3
Update Statement 4
4) Update Statement 4
Update Statement 5
5) Update Statement 5
Update Statement 6
6) Update Statement 6
Update Statement 7
7) Update Statement 7
Update Statement 8
8) Update Statement 8
Update Statement 9
9) Update Statement 9
Update Statement 10
10) Update Statement 10
Update Statement 11
11) Update Statement 11
Update Statement 12
12) Update Statement 12


table_hint syntax 
{ INDEX (index_val [,...n])
 | FASTFIRSTROW
 | HOLDLOCK  | PAGLOCK
 | READCOMMITTED
 | REPEATABLEREAD
 | ROWLOCK

query_hint syntax
query_hint syntax

IDENTITY column

Unlike an INSERT statement, an UPDATE statement cannot update the values of an IDENTITY column. In the next lesson, you will learn about the DELETE statement.