SQL UPDATE Query

What is UPDATE query?

Definition: UPDATE query means to execute query which modify records in database table.

The fundamental use of UPDATE query is to modify existing records into table. When is come to edit or modify any data in database table, we can use UPDATE sql query.

There are two ways to use UPDATE query in sql.

  • First is without where clause where it update all records in database table.
  • Another is we can use where clause to define which rows we need to edit. We can either
    update single row or multiple row by using IN condition with where clause. We will learn with example in this chapter.

Why UPDATE query needed?

Sometime it may happen that wrong data stored into database table or it might happen that system required to edit some old data with new data, in this case we can use UPDATE query to edit records into database table.

Syntax for UPDATE query

There are mainly two options to use UPDATE query in sql as following.

  • Update all rows
    In this option, we can update all exiting records into database table as per our requirements.

    UPDATE TABLENAME
    SET ColumnName1=Value1, ColumnName2=Value2, ColumnName3=Value3 ......
  • Update selected row/rows by using where clause
    UPDATE TABLENAME
    SET ColumnName1=Value1, ColumnName2=Value2, ColumnName3=Value3 ......
    WHERE CONDITION;

When we use where clause, there is another option where we can add different type of condition like to edit particular records or to edit rang or records by using IN operator. We can use following syntax to perform UPDATE query.


UPDATE TABLENAME
SET ColumnName1=Value1, ColumnName2=Value2, ColumnName3=Value3 ......
WHERE CONDITION;


Let’s understand UPDATE query with few example. We will use same Employee table with following data.

Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter Male 10-11-1982
2 EMP_1002 Josh Wills Male 09-07-1980
3 EMP_1003 Jeremy Parkar Female 08-09-1990


Example 1: Without “WHERE” clause

UPDATE Employee
SET EmployeeCode = 'EMP_1004',
FirstName = 'Hello',
LastName = 'World',
Gender = 'Male',
BirthDate = '10-10-1990'

In above UPDATE query, we can see there is no any “WHERE” clause so this will update all records which are defined in query. We will be able to see following output result after execution of above UPDATE query.


Id Employee Code First Name Last name Gender Birth Date
1 EMP_1004 Hello World Male 10-10-1990
2 EMP_1004 Hello World Male 10-10-1990
3 EMP_1004 Hello World Male 10-10-1990


Example 2: Without “WHERE” clause

UPDATE Employee
SET EmployeeCode = 'EMP_1004',
FirstName = 'Hello',
LastName = 'World',
Gender = 'Female',
BirthDate = '10-10-1990'
WHERE Id = 1

In above UPDATE query, we can see there is “WHERE” clause so this will update only one row data which are defined in query. We will be able to see following output result after execution of above UPDATE query. In condition, we have used Id=1 so it will update only data or row which has Id=1 value. In below output result, you can see green colour highlighted row is updated only.


Id Employee Code First Name Last name Gender Birth Date
1 EMP_1004 Hello World Female 10-10-1990
2 EMP_1002 Josh Wills Male 09-07-1980
3 EMP_1003 Jeremy Parkar Female 08-09-1990


Example 3: Without “WHERE” clause and range of field (Id) value

UPDATE Employee
SET EmployeeCode = 'EMP_1004',
FirstName = 'Hello',
LastName = 'World',
Gender = 'Female',
BirthDate = '10-10-1990'
WHERE Id IN (1,2)

In above UPDATE query, we can see there is “WHERE” clause with range of Id having two values (1,2) so this will update those rows data which has Id values 1 and 2 in query. We will be able to see following output result after execution of above UPDATE query. In condition, we have used Id with IN operator also so it will update those data or row which has Id=1 and Id=2 value. In below output result, you can see green colour highlighted row is updated only.


Id Employee Code First Name Last name Gender Birth Date
1 EMP_1004 Hello World Female 10-10-1990
2 EMP_1004 Hello World Female 10-10-1990
3 EMP_1003 Jeremy Parkar Female 08-09-1990

IMPORTANT: If you update all records without using WHERE clause, check your sql update query twice to make sure you are doing right things.