SQL- How to Update Data in a Table

Once you insert data in a table using INSERT DML command, you may wish to update the data. The values to be changed can be a few columns in all rows or few columns in specific rows.  In this tutorial you will learn How to Update Data in a Table. Update process is required when users insert incorrect data or when values change after data insertion. For example if an employee is promoted and his designation change, it must be updated in employee table.

Syntax of Update Statement to Update Data in a Table

UPDATE table-name
SET
Column-name1=new-column-value1,
Column-name2=new-column-value,…...
[WHERE condition]

• UPDATE- keyword
• table-name- name of the table in which you wish to update data
• SET – keyword
• column-name- is name of the column whose value is to be updated
• new-column-value- is the new value to replace the old value
• WHERE(optional)- is the optional clause of UPDATE statement. The update of data will be for the rows for which the condition in WHERE clause becomes TRUE.

 Examples of Updating Data in a Table

Continuing with the tables created in CREATE TABLE and INSERT statements tutorials, here are a few examples to update the data.

To update a specific column in all rows with same value

When WHERE Clause is not given in the UPDATE statement the old column values are replaced with new values in all the rows of the table

Before update

select SPCOMM  from salestbl;

After Update

UPDATE  salestbl SET SPCOMM =0.3;
select SPCOMM from Salestbl;

To update a specific column in selected rows

“Where clause” of the UPDATE statement is used as a filter to apply changes in only selected rows on the basis of a condition. Only those rows are updated which satisfy the condition

Before Update

select SPCITY, SPCOMM from Salestbl;

Displays same commission for sales persons of all cities

After Update

update Salestbl SET SPCOMM =0.5 where SPCITY='New York';
select SPCITY,    SPCOMM from Salestbl;

Displays updated commission for sales persons belonging to ‘New York’ city

To update multiple columns in selected rows

You can update multiple columns in the UPDATE statement by giving Column-name=Value pairs. These must be separated by commas. You can also specify condition for update in WHERE Clause.

Before Update

select * from customertbl ;

After Two Update Statements

UPDATE  customertbl set CUSTPHONE='038-3738-2727', CUSTCREDITSCORE=9.2 where custid=103;
UPDATE  customertbl set CUSTPHONE='034-2222-5555', CUSTCREDITSCORE=7, CUSTCITY='Maryland' where custid=102;
Select * from customertbl;

Points to Remember for Update Data in a Table

• You can specify multiple ‘column-name= new-value’ pairs separated by commas.
• You can update data that already exists in a table.
• If you omit the WHERE Clause you will update column(s) of all the rows in the table.