SQL UPDATE Statement Tutorial

Introduction

 
In the previous chapter, we learned about INSERT INTO Statement in SQL and how to use INSERT INTO Statement.
In this chapter, we will learn how to UPDATE SQL works and different options used with the UPDATE statement.
 

UPDATE statement in SQL

 
The SQL UPDATE statement is used to modify the existing data records in a table. The SQL UPDATE statement is used to update data from a database table or tables.
 
 
SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.
 
The SQL UPDATE statement has the following form SQL Update statement is used to update records from an RDBMS( Relational Database Management System) data table.
 
The SQL UPDATE statement can be UPDATE records, UPDATE a set of records based on a condition, filter records, sort records, group by records, and more.
 
Here are some of the use cases of the SQL UPDATE statement.
 
Note: The UPDATE statement is used to update records in a table! Notice the WHERE clause in the UPDATE statement.
 
The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!
 
Syntax   
  1. UPDATE table_name      
  2. SET column1 = value1, column2 = value2, ...      
  3. WHERE condition;   

The UPDATE statement is typically in three parts:

  1. The tableName to update
  2. The SET clause which specifies the columns to update
  3. The WHERE clause, which specifies which rows to include in the update open
Example 

 

UPDATE multiple records statement 

 
This is the WHERE clause that determines how many records will be updated
 
The following SQL statement will update the empName to "Manoj" for all records where EmpCity is "Noida" 
 
Syntax 
  1. USE Sample;      
  2. GO      
  3. UPDATE EmployeeDetail      
  4. SET EmpName='Manoj' Where EmpAddress='Noida'     
  5. GO       
Example 
                                                      
 
UPDATE the WHERE  clause 
 
In this example, you can use the WHERE clause to specify which rows to update.
 
This statement updates the value in the  EmpName of the EmployeeDetail
 
EmployeDetail table for all rows that have an existing value of 'Noida' in the EmpAddress and have a value in theEmpName column that starts with EmpAddress 
  1. USE Sample;      
  2. GO      
  3. UPDATE EmployeeName.Name      
  4. SET EmpName = 'Rahul'      
  5. WHERE Name LIKE N'R' AND Address= 'Noida';      
  6. GO     

UPDATE TOP statement in SQL

 
The TOP Statement to limit the number of rows that are modified in an UPDATE statement. When a TOP (n) clause is used with UPDATE, the update operation is performed on a random selection of 'n' number of rows.
 
The following example updates the top  3 random rows in the EmployeDetails table
 
Syntax 
  1. UPDATE top (3)  EmployeeDetail set EmpAddress = 1   
Example 
 
 
The TOP Statement applies updates in a meaningful chronology, you must use TOP together with ORDER BY in a subselect statement.
 
The following example updates the TOP 3 EmpAddress.  
 

UPDATE statement with label

 
The Following is an example For LABEL for the UPDATE statement.
 
Syntax   
  1. UPDATE EmployeeDetail      
  2. SET  EmpName = 'Rahul'      
  3. WHERE EmpId = 1     
  4. OPTION (LABEL = N'label1');    
Example 
 
 
 

UPDATE - Specifying a computed value 

 
The following examples use computed values in an UPDATE statement. The example doubles the value in the EmployeeName column for all rows in the EmployeeDetails table.
  
Syntax  
  1. UPDATE EmployeeDetail    
  2. SET EmpName = 'Rohan'    
  3. WHERE EmpId = 1;     
Example 
 
 

UPDATE - Specifying a compound operator 

 
The Specifying Compound operators execute some operation and set an original value to the result of the operation. 
 
For example, if a variable @x equals 34, then @x += 2 takes the original value of @x, adds 2, and sets @x to that new value (36).
 
Syntax 
 
 
 

UPDATE table with data from another table  

  
The UPDATE statement is performing a traditional update or updating one table with data from another table. 
 
Syntax 
  1. UPDATE table1    
  2. SET column1 = (SELECT expression1    
  3.                FROM table2    
  4.                WHERE conditions)    
  5. [WHERE conditions];      
Example
 
 

UPDATE Rows in a Remote Tables  

 
The  UPDATE rows in a remote target table by using a linked server or a rowset function to reference the remote table. 
 

UPDATE to modify FILESTREAM data 

 
The UPDATE statement has used the data in the file system file. We do not recommend this method for streaming large amounts of data to a file. Use the appropriate Win32 interfaces. The following example replaces any text in the file record with the text Xray 1. For more information,    
  1. UPDATE  Name.EmployeeDetail      
  2. SET [Chart] = CAST('Xray 1' as varbinary(max))      
  3. WHERE [EmployeeDetail] = 2;   

UPDATE The Data Using a system data type  

 
We can UPDATE a UDT(user-defined type) by supplying a value in a SQL Server system data type, as long as the user-defined type supports implicit or explicit conversion from that type.
 
The following example shows how to update a value in a column of user-defined type Point, by explicitly converting from a string. 
  1. UPDATE dbo.EmployeeDetail      
  2. SET EmpName = CONVERT(Point, '12.3:46.2')      
  3. WHERE EmpName = 'Anchorage';   

Update - Modifying the value of a property or data member 

 
The UPDATE  modifies a UDT by modifying the value of a registered property or public data member of the user-defined type.
 
The expression supplying the value must be implicitly convertible to the type of the property. The following example modifies the value of property X of user-defined type Point. 
  1. UPDATE EmployeeDetail      
  2. SET  EmpCity.X = 23.5      
  3. WHERE EmpName = 'Manoj';   

Update - Overriding the Default Behavior of the Query Optimizer by Using Hints

 
This section demonstrates how to use tables and query hints to temporarily override the default behavior of the query optimizer when processing the UPDATE statement.
  
CAUTION
 
The SQL SERVER query optimizer typically selects the best execution plan for a query,
 
we recommend that hints be used only as a last resort by experienced developers and database administrators.
 

Update Warning! 

 
Note: Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!
 
Syntax  
  1. UPDATE table1    
  2. SET column1 = (SELECT expression1    
  3.                FROM table2    
  4.                WHERE conditions)    
  5. [WHERE conditions];   
Example 
 
  

Summary

 
In the next chapter, we will learn how to use SQL Replace Statement.
Author
Naresh Beniwal
Editor 7.1k 1.5m
Next » SQL Replace Statement

©2024 C# Corner.