How to insert values to newly added column-CodePudding

I am new to oracle thus this question.

There is a table already existed and I have added a new column to it. There are 5 rows and I do not want to use update table with where clause to insert the values one by one in the new column. Is there a statement like INSERT ALL to insert the values into the new column in one shot ?

Thanks

CodePudding user response：

You can also use something like below which in-effect I would say is multiple update only, wrapped in single statement.

SQL> select * from test_upd;

       ID1        ID2
---------- ----------
         1
         2
         3
         4

SQL> update test_upd a set a.id2 =
  2  (select
  3  case
  4  when id1=1 then 100
  5  when id1=2 then 200
  6  when id1=3 then 300
  7  else 5000 end
  8  from test_upd b
  9  where a.id1=b.id1);

4 rows updated.

SQL> select * from test_upd;

       ID1        ID2
---------- ----------
         1        100
         2        200
         3        300
         4       5000

CodePudding user response：

Use a MERGE statement:

MERGE INTO your_table dst
USING (
  SELECT 1 AS id, 'aaa' AS newvalue FROM DUAL UNION ALL
  SELECT 2, 'bbb' FROM DUAL UNION ALL
  SELECT 3, 'ccc' FROM DUAL UNION ALL
  SELECT 4, 'ddd' FROM DUAL UNION ALL
  SELECT 5, 'eee' FROM DUAL
) src
ON (dst.id = src.id)
WHEN MATCHED THEN
  UPDATE SET value = src.newvalue;

Which, if you have the table:

CREATE TABLE your_table (id, value) AS
SELECT 1, CAST(NULL AS VARCHAR2(3)) FROM DUAL UNION ALL
SELECT 2, NULL FROM DUAL UNION ALL
SELECT 3, NULL FROM DUAL UNION ALL
SELECT 4, NULL FROM DUAL UNION ALL
SELECT 5, NULL FROM DUAL;

Then, after the MERGE, the table contains:

ID VALUE

1 aaa

2 bbb

3 ccc

4 ddd

5 eee

ID	VALUE
1	aaa
2	bbb
3	ccc
4	ddd
5	eee

db<>fiddle here