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- 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
select SPCOMM from salestbl;
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
select SPCITY, SPCOMM from Salestbl;
Displays same commission for sales persons of all cities
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.
select * from customertbl ;
|103||Sujob Day||Los Angeles||–||–|
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;
|103||Sujob Day||Los Angeles||038-3738-2727||9|
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.