SQL UPDATE Statement Tutorial


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!
  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


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" 
  1. USE Sample;      
  2. GO      
  3. UPDATE EmployeeDetail      
  4. SET EmpName='Manoj' Where EmpAddress='Noida'     
  5. GO       
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
  1. UPDATE top (3)  EmployeeDetail set EmpAddress = 1   
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.
  1. UPDATE EmployeeDetail      
  2. SET  EmpName = 'Rahul'      
  3. WHERE EmpId = 1     
  4. OPTION (LABEL = N'label1');    

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.
  1. UPDATE EmployeeDetail    
  2. SET EmpName = 'Rohan'    
  3. WHERE EmpId = 1;     

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).

UPDATE table with data from another table  

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

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.
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!
  1. UPDATE table1    
  2. SET column1 = (SELECT expression1    
  3.                FROM table2    
  4.                WHERE conditions)    
  5. [WHERE conditions];   


In the next chapter, we will learn how to use SQL Replace Statement.
Naresh Beniwal
374 6.8k 1m
Next » SQL Replace Statement