SQL UPDATE Statement

UPDATE statement in SQL

The SQL UPDATE statement updates existing data records in a table or view in SQL Server. This tutorial teaches you how SQL UPDATE works and what are different options to use with SQL UPDATE are. Here is the simplest form of an UPDATE statement in SQL.

SQL UPDATE Syntax:

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

In the above syntax, the UPDATE keyword is followed by a table or view name. The SET keyword defines what column values are to be changed with the values, and the WHERE clause filters the criteria of records.

Here is an example.

UPDATE EmployeeDetail
SET EmpName='Ravi', EmpCity='Noida'
Where EmpId=1

In the above example, we have a table named EmployeeDetail. We change the EmpName column value to 'Ravi' and the EmpCity column value to 'Noida' where EmpId is 1. The above query will replace the existing EmpName and EmpCity with these new values for EmpId = 1 record.

The output looks like the following:

                         

UPDATE multiple records in SQL

A SQL UPDATE statement can update multiple records in a single statement depending on the WHERE clause. For example, if a WHERE clause filters n rows, all n rows will get updated with the new values. The following SQL statement updates the empName to 'Manoj' for all records where EmpCity is 'Noida'.

UPDATE EmployeeDetail
SET EmpName='Manoj' Where EmpCity='Noida'

UPDATE TOP statement in SQL

Let's say you do not wish to update all records of a matching WHERE clause, but you only want to update top n records. You can do that by using the UPDATE TOP statement. The UPDATE TOP statement in SQL limits the number of rows modified by an UPDATE statement. When a TOP (n) clause is used with UPDATE, the update operation is performed on a random selection of the 'n' number of rows.

The following example updates the top  3 random rows in the EmployeDetails table.

UPDATE top (3)  EmployeeDetail set EmpAddress = 1

The output looks like the following:

If you want to order the selected records, you can use TOP with the ORDER BY clause.

Summary

In this tutorial, we learned the basics of the SQL UPDATE statement. We saw how to update some or all records of a database table using the UPDATE SQL. 


Similar Articles