Update Multiple Columns In a Table

In the post Update a Table Based on Another Table, we showed how to update a column in a table based on the result from a select query. To update multiple columns at the same time, we simply specify multiple columns after "set" and "select".

SQL> select * from tbl_a order by id;

        ID VALUE            VALUE2
---------- ---------------- ----------------
         1 A
         2 B
         3 C
         4 D

SQL> update tbl_a a set (value, value2) 
                        = ( select a.value||'_1', a.value||'_2' 
                            from tbl_a b where a.id=b.id);

4 rows updated.

SQL> select * from tbl_a order by id;

        ID VALUE            VALUE2
---------- ---------------- ----------------
         1 A_1              A_2
         2 B_1              B_2
         3 C_1              C_2
         4 D_1              D_2

Update a Table Based on Another Table

It is a common task to update records in one table based on those in another table. We can use "update" query to do this. Suppose we have two tables as shown below.

SQL> select * from tbl_1 order by id;

        ID VALUE
---------- --------------------------------
         1 A
         2 X
         3 Y
         4 D

SQL> select * from tbl_2 order by id;

        ID VALUE
---------- --------------------------------
         2 B
         3 C

We want to update the val in tbl_1 with the value in tbl_2 based on id. The desirable result for tbl_1 after the update will be:

       ID VALUE
---------- --------------------------------
         1 A
         2 B
         3 C
         4 D

One way to do this is the following query.

SQL> update tbl_1 a 
set value=(select value from tbl_2 b where a.id=b.id) 
where exists ( select 1 from tbl_2 b where a.id=b.id);

2 rows updated.

SQL> select * from tbl_1 order by id;

        ID VAL
---------- --------------------------------
         1 A
         2 B
         3 C
         4 D

Please notice in the update query, only 2 rows are updated due to the "where exists" clause. If we do not include the "where exists" clause, records whose id are not in tbl_2 will have null values as shown below.

SQL> update tbl_1 a set val=(select val from tbl_2 b where a.id=b.id) ;

4 rows updated.

SQL> select * from tbl_1 order by id;

        ID VALUE
---------- --------------------------------
         1
         2 B
         3 C
         4

In the above update query, 4, instead of 2, rows are updates due to the removal of the "where exists" clause. We can change the above update query without using "where exists" to make it work as the following.

SQL> update tbl_1 a set val=nvl((select value from tbl_2 b where a.id=b.id),a.value) ;

4 rows updated.

SQL> select * from tbl_1 order by id;

        ID VALUE
---------- --------------------------------
         1 A
         2 B
         3 C
         4 D

In the above update query, we added function nvl((select ...), a.value) which means if the select query returns no result (NULL), values will be set as the original values. All four rows are updated. The first update query with "where exists" clause is the best because it only update rows whose id in tbl_2.

Insert Data into an Oracle View

We are able to insert records into a simple view. By doing that, the data are actually inserted into the physical table that the view is based on. The following is an example.

SQL> create table tbl_test (id number, value varchar2(64));

Table created.

SQL> create view v_tbl_test as select * from tbl_test;

View created.

SQL> insert into tbl_test values(1,'Hello');

1 row created.

SQL> insert into v_tbl_test values(2,'World');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tbl_test order by id;

        ID VALUE
---------- ----------------------------------------------------------------
         1 Hello
         2 World

SQL> select * from v_tbl_test order by id;

        ID VALUE
---------- ----------------------------------------------------------------
         1 Hello
         2 World