Update statement in oracle

Update statement in oracle is used to update the existing rows in the table. It is a DML statement. DML stand data manipulation language. We can use this statement to update specific rows ,set of rows or all rows in the table.

Syntax is given below

Table : It is the table name which need to be updated
column : it is the single column or multiple column in the table which need to be updated
value: It is the value of column to be updated. we can use subquery to get that value also
Condition : It is the where condition which decide what all rows will be updated. It can be composed of column names,expression subqueries and comparison operators

Important point
1) Update statement after completion will shows how many rows updated unless you have issued set feedback off
2) If no rows updated, it will display 0 rows updated
3) The changes made are visible in your session and it is not visible to other session in the database
4) You need to execute “commit;” statement to make the changes permanent in the database or “rollback;” to rollback the changes done
5) it is good to first execute the same where condition with select to find out the number of rows and rows which is going to be updated before issuing update statement
6) If you are targeting for single rows, use primary key if possible

SQL> select * from emp where EMPNO=7844;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ————— ———- ———-
DEPTNO
———-
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

SQL> update emp set SAL=15000 where EMPNO=7844;

1 row updated.

SQL> select * from emp where EMPNO=7844;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ————— ———- ———-
DEPTNO
———-
7844 TURNER SALESMAN 7698 08-SEP-81 15000 0
30

SQL> commit;

Commit complete.

Update statement using subquery

We can use subquery inside the update statement also.

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ————— ———- ———-
DEPTNO
———-
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10

2 rows selected.

SQL> update emp set sal=(select sal from emp where empno=7934) where EMPNO=7902;

1 row updated.

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ————— ———- ———-
DEPTNO
———-
7902 FORD ANALYST 7566 03-DEC-81 1300
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10

2 rows selected.

SQL> commit;

Commit complete.

Update Statement with out where clause

You can update all the rows in the table by not specifying any where clause in the update statement

Update FND_USER set end_date=’1-JUN-2018′  ;

The above statement would update all the rows in the table FND_USER

 

Update Statement with multiple column

You can specify multiple column in the same update statement

Update FND_USER set end_date=’1-JUN-2018′ , password=” ;

The above statement would update all the rows in the table FND_USER with specified end_date and null all the password

Leave a Reply