UPDATE Query in DBMS

UPDATE Query in DBMS

 

In this article, we will learn about UPDATE Query in DBMS.

In the Real world scenario, facebook, Gmail will  give an option to update your name or  Profile picture, etc, how does it work, at the backend SQL UPDATE  is executed internally

  •  The UPDATE  statement is used to modify or change the data of the existing table in the database
  • We can update a single column as well as multiple columns as per our requirement

The general syntax of UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
  • The SET  is Is used to set new values to the required column and the where Clause is used to filter the rows for which rows of data are needed to be updated

 

UPDATE Query in DBMS

Updating a single column

Any row in the database table can be updated using update  statement

Consider a sample table EMP as shown below

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7698BLAKEMANAGER783901-MAY-81285030
7782CLARKMANAGER783909-JUN-81245010
7566JONESMANAGER783902-APR-81297520
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020
7369SMITHCLERK790217-DEC-8080020

Update the commission of all employees to 200/-  whose salary is less than 3000/-

UPDATE emp SET comm=200 
WHERE sal<3000;

O/P

7 ROWS SELECTED
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7698BLAKEMANAGER783901-MAY-81285020030
7782CLARKMANAGER783909-JUN-81245020010
7566JONESMANAGER783902-APR-81297520020
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020
7369SMITHCLERK790217-DEC-8080020020

Initially, in the EMP table, values are NULLfor comm column for all the rows but using update statement we have changed  the commission to 200 from NULL  for those employees who are having salaries less than 3000.

 

Updating multiple columns

Using a single update statement with can parallelly update any number of column

Update salary by 500 and change the commission to 1000 for all analysts working in the company

update emp set sal=sal+500 ,comm=1000
where job='ANALYST';

O/P

2 rows selected.
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7788SCOTTANALYST756619-APR-873500100020
7902FORDANALYST756603-DEC-813500100020

We have two analysts, hence two rows will be updated i.e salary will be increased from 3000 to 3500 and commission will be changed to 1000 i.e both salary and commission columns get updated.

 

Updating without where clause

If you don’t specify where clause, it means that no condition is required and no data filtering happens and all the rows present in the table will be updated for that column as specified

Make the salary of all employees working in the company to 500

update emp 
set sal=500;

O/P

7 rows selected
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7839KINGPRESIDENT17-NOV-8150010
7698BLAKEMANAGER783901-MAY-8150020030
7782CLARKMANAGER783909-JUN-8150020010
7566JONESMANAGER783902-APR-8150020020
7788SCOTTANALYST756619-APR-8750020
7902FORDANALYST756603-DEC-8150020
7369SMITHCLERK790217-DEC-8050020020

We have seven rows present in the table, the value of a salary in every row becomes 500 because there is no condition for data change, hence all the rows will be updated