This SQL tutorial provides explanations, examples for 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 oracle 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 in oracle 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
Related Articles
INSERT statement in Oracle
Delete from table statement in Oracle
oracle create table example
Alter Table in Oracle
alter table add column oracle
Oracle documentation on Update