SQL Update Statement

UPDATE command is used to modify an existing record in the table.

Syntax:-


UPDATA table_name
SET column_1=new_value_1,
column_2=new_value_2,
column_3=new_value_3
...................
...................
column_n=new_value_n

WHERE [CONDITION]

Example:-

Suppose you have two records in the employees table


 SELECT * FROM employees

Output:-


+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | Virat      | Kohli     | virat@abc.com  | Delhi      |
|  2 | rohit      | Sharma    | rohit@abc.com  | Mumbai     |
+----+------------+-----------+----------------+------------+

Apply WHERE Condition

FIRST CASE:- Now, You want to update the address of Virat from Delhi to Mumbai



UPDATE employees SET address='Mumbai' where id=1

Now, check the table


+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | Virat      | Kohli     | virat@abc.com  | Mumbai     |
|  2 | rohit      | Sharma    | rohit@abc.com  | Mumbai     |
+----+------------+-----------+----------------+------------+

Without WHERE Condition

SECOND CASE:- If you do not use where condition then the record will be update in all rows.

Example:- If you want to update email of rohit@abc.com to rohitsharma@abc.com without where condition.


UPDATE employees SET email='rohitsharma@abc.com'

Now check the updated results in the employees table


 SELECT * FROM employees

Output:-



+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | Virat      | Kohli     | rohitsharma@abc.com | Mumbai|
|  2 | rohit      | Sharma    | rohitsharma@abc.com | Mumbai|
+----+------------+-----------+----------------+------------+

Note:- If you do not use WHERE condition on UPDATE Statement then it will be reflected in all rows, so please update the record carefully.

SQL Update Statement – Interview Questions

Q 1: What does UPDATE do?

Ans: It modifies existing records.

Q 2: Syntax of UPDATE?

Ans: UPDATE table SET column=value WHERE condition;

Q 3: Is WHERE clause mandatory?

Ans: Not mandatory, but recommended to avoid updating all rows.

Q 4: Can multiple columns be updated?

Ans: Yes, in a single query.

Q 5: Can UPDATE be rolled back?

Ans: Yes, if transactions are used.