How to add new column in a table from existing column in the same table?

Question

I have a table with only 1 column which has several details concatenated into it like Product Name, Shipment Date etc:

MASTERCOLUMN
Row1_Prod1_ShipDate_01-Dec-21
Row2_Prod2_ShipDate_03-Dec-21
Row3_Prod3_ShipDate_07-Dec-21
.
.

The requirement is to add another 2 columns containing only ProductName and ShipmentDate details corresponding to MASTERCOLUMN: (Eg below)

MASTERCOLUMN                    ProductName   ShipmentDate
Row1_Prod1_ShipDate_01-Dec-21   Prod1         01-Dec-21
Row2_Prod2_ShipDate_03-Dec-21   Prod2         03-Dec-21
Row3_Prod3_ShipDate_07-Dec-21   Prod3         07-Dec-21
.
.
.

I've altered the table and added these new columns. Is there any way of updating the table so that corresponding values gets copied to these new columns? Can this be achieved using SQL query? I am using SQL Developer with Oracle 19.c version.

I've tried updating the column after adding the 2 columns.. The below sample query is for updating the ProductName column:

UPDATE TABLE
SET ProductName = (SELECT SUBSTR(MASTERCOLUMN,6,5) FROM TABLE);

But this returns ORA-01427: single-row subquery returns more than one row error.

However, I tried to update only one row and it worked fine;

UPDATE TABLE
SET ProductName = (SELECT SUBSTR(MASTERCOLUMN,6,5) FROM TABLE)
WHERE MASTERCOLUMN = 'Row1_Prod1_ShipDate_01-Dec-21';

But I need to populate the entire table with corresponding values from MASTERCOLUMN column..

Always same positions? https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SUBSTR.html#GUID-C8A20B57-C647-4649-A379-8651AA97187E — jarlh, Dec 22 '21 at 16:53
In stead of substring, you could also take a look at [Split String by delimiter position using oracle SQL](https://stackoverflow.com/questions/26878291/split-string-by-delimiter-position-using-oracle-sql) — Luuk, Dec 22 '21 at 16:55
Don't store same data twice. Create a view instead, or have virtual columns (or whatever Oracle call them.) — jarlh, Dec 22 '21 at 17:00
Change the design and store the values in different columns from the start. It's easier to concatenate them back together when needed than splitting them. You can also easily use indexes on them to speed up queries filtering on them. — sticky bit, Dec 22 '21 at 18:42

score 1 · Accepted Answer · answered Dec 22 '21 at 17:38

Don't use subquery, you don't need it.

Sample table and rows:

SQL> create table test (mastercolumn varchar2(30));

Table created.

SQL> insert into test
  2    select 'Row1_Prod1_ShipDate_01-Dec-21' from dual union all
  3    select 'Row2_Prod2_ShipDate_03-Dec-21' from dual union all
  4    select 'Row3_Prod3_ShipDate_07-Dec-21' from dual;

3 rows created.

Add new columns:

SQL> alter table test add
  2    (product_name varchar2(10),
  3     shipmentdate date);

Table altered.

Update:

SQL> update test set
  2    product_name = substr(mastercolumn,
  3                          instr(mastercolumn, '_', 1, 1) + 1,
  4                          instr(mastercolumn, '_', 1, 2) - instr(mastercolumn, '_', 1,1 ) - 1
  5                         ),
  6    shipmentdate = to_date(substr(mastercolumn, -9), 'dd-mon-yy', 'nls_date_language=english');

3 rows updated.

Result:

SQL> select * From test;

MASTERCOLUMN                   PRODUCT_NA SHIPMENTDA
------------------------------ ---------- ----------
Row1_Prod1_ShipDate_01-Dec-21  Prod1      01.12.2021
Row2_Prod2_ShipDate_03-Dec-21  Prod2      03.12.2021
Row3_Prod3_ShipDate_07-Dec-21  Prod3      07.12.2021

SQL>

But, as you've already been told, you'd rather choose another approach, one of them being a view:

SQL> create or replace view v_test as
  2    select mastercolumn,
  3           substr(mastercolumn,
  4                  instr(mastercolumn, '_', 1, 1) + 1,
  5                  instr(mastercolumn, '_', 1, 2) - instr(mastercolumn, '_', 1,1 ) - 1
  6                  ) as product_name,
  7           to_date(substr(mastercolumn, -9), 'dd-mon-yy', 'nls_date_language=english')
  8             as shipmentdate
  9  from test;

View created.

SQL> select * From v_test;

MASTERCOLUMN                   PRODUCT_NAME         SHIPMENTDA
------------------------------ -------------------- ----------
Row1_Prod1_ShipDate_01-Dec-21  Prod1                01.12.2021
Row2_Prod2_ShipDate_03-Dec-21  Prod2                03.12.2021
Row3_Prod3_ShipDate_07-Dec-21  Prod3                07.12.2021

SQL>

As suggested, I created a view and it looks perfect! Thanks! — Irfan Basha, Dec 23 '21 at 13:35

How to add new column in a table from existing column in the same table?

1 Answers1