UPDATE Query in DBMS

UPDATE 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

Updating a single column

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

update
Consider a sample table EMP as shown below
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20

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
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 200 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 200 10
7566 JONES MANAGER 7839 02-APR-81 2975 200 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 200 20
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.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7788 SCOTT ANALYST 7566 19-APR-87 3500 1000 20
7902 FORD ANALYST 7566 03-DEC-81 3500 1000 20
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
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT 17-NOV-81 500 10
7698 BLAKE MANAGER 7839 01-MAY-81 500 200 30
7782 CLARK MANAGER 7839 09-JUN-81 500 200 10
7566 JONES MANAGER 7839 02-APR-81 500 200 20
7788 SCOTT ANALYST 7566 19-APR-87 500 20
7902 FORD ANALYST 7566 03-DEC-81 500 20
7369 SMITH CLERK 7902 17-DEC-80 500 200 20
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

Please Login/Signup to comment