Updating Data in SQL Tables: A Comprehensive Guide

The SQL UPDATE Query is used to modify the existing records in a table. You can use the WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows would be affected.

Syntax: UPDATE Syntax

<span class="pln">
UPDATE table_name
SET column1 </span><span class="pun">=</span><span class="pln"> value1</span><span class="pun">,</span><span class="pln"> column2 </span><span class="pun">=</span><span class="pln"> value2</span><span class="pun">,</span><span class="pln"> </span><span class="pun">...</span><span class="pln">
WHERE condition</span><span class="pun">;</span><span class="pln">
</span>

Prerequisite Codes

If you don't have table please create below table and practice it.

My database name is SQLExamples

<span class="pln">

USE </span><span class="typ">SQLExamples</span><span class="pln">
</span>

Create this below Employee table

<span class="pln">
DROP TABLE </span><span class="typ">Employee</span><span class="pln">
CREATE TABLE </span><span class="typ">Employee</span><span class="pun">(</span><span class="pln">
</span><span class="typ">EmpId</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln">
</span><span class="typ">EmpName</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">25</span><span class="pun">),</span><span class="pln">
</span><span class="typ">EmpAddress</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">100</span><span class="pun">),</span><span class="pln">
</span><span class="typ">EmpDept</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">25</span><span class="pun">)</span><span class="pln">
</span><span class="pun">)</span><span class="pln">
</span>

Insert data inside table

<span class="pln">
INSERT INTO </span><span class="typ">Employee</span><span class="pln"> VALUES
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rambo'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'IT'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rohit'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'IT'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rohon'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ITIS'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Ronok'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ITIS'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">5</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rubin'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ITIS'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">6</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Sorif'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ADMIN'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">7</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Soriful'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ADMIN'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">8</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Sofik'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kolkata'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ADMIN'</span><span class="pun">)</span><span class="pln">

</span>

<span class="pln">
SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Employee</span><span class="pln">
</span>

Output:

The above code will produce the following result-

Example 1:

Update Employee Address where employee department is IT

Code:

<span class="pln">
UPDATE </span><span class="typ">Employee</span><span class="pln">
SET </span><span class="typ">EmpAddress</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Mumbai'</span><span class="pln">
WHERE  </span><span class="typ">EmpDept</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'IT'</span><span class="pln">
</span>

Output:

The above code will produce the following result-

<span class="pln">
</span><span class="pun">(</span><span class="lit">2</span><span class="pln"> row</span><span class="pun">(</span><span class="pln">s</span><span class="pun">)</span><span class="pln"> affected</span><span class="pun">)</span><span class="pln"> 
</span>

Example: 2

Update employee name and address where employee Id is 1

Code:

<span class="pln">
UPDATE </span><span class="typ">Employee</span><span class="pln">
SET </span><span class="typ">EmpName</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Ismile'</span><span class="pun">,</span><span class="pln"> </span><span class="typ">EmpAddress</span><span class="pun">=</span><span class="pln"> </span><span class="str">'Kerala'</span><span class="pln">
WHERE </span><span class="typ">EmpId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">1</span><span class="pln">
</span>

Output:

The above code will produce the following result-

<span class="pln">
</span><span class="pun">(</span><span class="lit">1</span><span class="pln"> row</span><span class="pun">(</span><span class="pln">s</span><span class="pun">)</span><span class="pln"> affected</span><span class="pun">)</span><span class="pln">
</span>

Example 3: Update Multiple Records

Code:

<span class="pln">
UPDATE </span><span class="typ">Employee</span><span class="pln">
SET </span><span class="typ">EmpAddress</span><span class="pun">=</span><span class="str">'Hydrabad'</span><span class="pln">
WHERE </span><span class="typ">EmpAddress</span><span class="pun">=</span><span class="str">'Kolkata'</span><span class="pun">;</span><span class="pln">
</span>

Output:

The above code will produce the following result-

<span class="pln">
</span><span class="pun">(</span><span class="lit">6</span><span class="pln"> row</span><span class="pun">(</span><span class="pln">s</span><span class="pun">)</span><span class="pln"> affected</span><span class="pun">)</span><span class="pln">
</span>

Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

Syntax:

<span class="pln">
 UPDATE </span><span class="typ">Employee</span><span class="pln">
SET </span><span class="typ">EmpName</span><span class="pun">=</span><span class="str">'Rambo Azmi'</span><span class="pln">
</span>

Output:

All the employee name will change to "Rambo Azmi"-