Showing posts with label update more than one colum. Show all posts
Showing posts with label update more than one colum. Show all posts

Sunday, September 28, 2014

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