UPDATE Query in DBMS

January 25, 2023

DBMS “UPDATE” Query

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
AND OR in DBMS

UPDATE Query In DBMS

  •  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
DBMS desktop gif
DBMS mobile adv gif

What is Database

Participation Constraint

DBMS Architecture

Data Models

Cardinality

Updating a single column

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

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

Prime Course Trailer

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

Get Prime
DBMS Tutorial banner

Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others

Get Prime
Course List
Course List

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout
Checkout