Oracle Update
examples and syntax explanation
Always test these 'oracle update' commands on a test server
before implementing them on a production server.
before implementing them on a production server.
The Oracle update statement allows you to change the data inside a table.
Syntax
update <table_name> set <column list> = <value list> where <restriction clause>;
If you do not specify a where clause, all rows in the table will be updated.
Permissions
You must have update privileges to update another schema's table.
If you want to update through a view, the view's owner needs to have
update permissions on the base table and you need update privileges on the view.
The 'update any table' privilege sidesteps the 2 requirements above by combining them in one privilege.
Examples
Let's use table SCOTT.DEPT as our example table.
| DEPTNO | DNAME | LOC |
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
DEPT contains 3 columns (deptno, dname and loc) and 4 rows.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
I want to update the location for department 'SALES' to 'LONDON'.
SQL> update dept set loc = 'LONDON'
where dname = 'SALES'; 1 row updated. SQL> commit; Commit complete. SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES LONDON 40 OPERATIONS BOSTON
Let's update 2 columns at the same time:
SQL> update dept set loc = 'MIAMI', deptno = 50
where dname = 'SALES'; 1 row updated. SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 50 SALES MIAMI 40 OPERATIONS BOSTON SQL> commit; Commit complete.
Let's use a function to change the value of the location column:
SQL> update dept set loc = lower(loc) ;
4 rows updated.
SQL> commit;
Commit complete.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING new york
20 RESEARCH dallas
50 SALES miami
40 OPERATIONS boston
Lastly: do not forget to commit to make your changes permanent.
