CODEWITHSUNDEEP
sqloracle-database
corycorycory
corycorycory

Reputation: 1656

Is it possible to duplicate all values in a table while updating one or more columns

I have a table with many columns, and what I would like to do is duplicate all of the rows in the table, but also update one of the columns to a new value.

For example lets say I have the table below. I want to add to my table a duplicate of each row, except instead of BASIC access, it will have 'ADVANCED':

Before:

NAME, GENDER, ACCESS
----------------------
STEVE, MALE, BASIC
MOLLY, FEMALE, BASIC

After

NAME, GENDER, ACCESS
----------------------
STEVE, MALE, BASIC
MOLLY, FEMALE, BASIC
STEVE, MALE, ADVANCED
MOLLY, FEMALE, ADVANCED

Is there a way to do this without specifying all columns? I have 60 columns in the table, and the structure can change (meaning columns may be added, removed, renamed, etc).

Is it possible in Oracle SQL to automate this?

Upvotes: 0

Views: 130

Answers (3)

kanagaraj
kanagaraj

Reputation: 442

Try below method with anonymous block to avoid listing columns in insert statements 

CREATE TABLE ACCESS_CHN
(NAAME VARCHAR2(100),
GENDER VARCHAR2(20),
ACCCESS VARCHAR2(30))

INSERT into ACCESS_CHN values('STEVE','MALE','BASIC');

INSERT into ACCESS_CHN values('MOLLY','FEMALE','BASIC');

COMMIT;

DECLARE

column_list varchar2(2000):=NULL;
plsql_block VARCHAR2(1000);

BEGIN

select LISTAGG(column_name,',') within group (order by column_id)
into column_list
from user_tab_columns
where table_name='ACCESS_CHN';

plsql_block :=  'CREATE TABLE ACCESS_CHN_BKP  as select '|| column_list || ' from ACCESS_CHN';

EXECUTE IMMEDIATE plsql_block;

plsql_block := 'UPDATE ACCESS_CHN_BKP  set ACCCESS=''ADVANCED'' ';

EXECUTE IMMEDIATE plsql_block;

COMMIT;

plsql_block :=  'CREATE TABLE ACCESS_CHN_FINAL  as select * from ACCESS_CHN
union all
select * from ACCESS_CHN_BKP';

EXECUTE IMMEDIATE plsql_block;

END;

--To rerun drop tables ACCESS_CHN_BKP and ACCESS_CHN_FINAL

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

Without specifying all the columns? With some help of a "temporary" table, here's how:

Your current table:

SQL> create table test
  2    (name varchar2(10),
  3     gender varchar2(20),
  4     caccess varchar2(20));

Table created.

SQL> insert into test
  2    select 'steve', 'male', 'basic' from dual union all
  3    select 'molly', 'female', 'basic' from dual;

2 rows created.
  • Create a "temporary" table as a copy of the "original" table
  • update column you want to modify
  • copy the whole "temporary" table to the "original"
  • drop the "temporary" table
SQL> create table test_temp as select * From test;

Table created.

SQL> update test_temp set caccess = 'advanced';

2 rows updated.

SQL> insert into test select * From test_temp;

2 rows created.

SQL> drop table test_Temp;

Table dropped.

SQL> select * From test;

NAME       GENDER               CACCESS
---------- -------------------- --------------------
steve      male                 basic
molly      female               basic
steve      male                 advanced
molly      female               advanced

SQL>

Apparently, that works, but - what if the original table is huge? It takes a lot of space, and its copy takes approximately twice as much. Why are you doing that, anyway?

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Just use insert . . . select:

insert into t (name, gender, access)
    select name, gender, 'ADVANCED'
    from t;

You need to list all the columns. You can shorten the manual process by using a query to generate the list. If you had to do this a lot and always knew you were leaving out access and access is the last column, you could use a view:

create view v_t as
    select . . . -- all but access
    from t;

insert into t ( . . . )
    select v.*, 'ADVANCED'
    from v_t;

Or you could use dynamic SQL to generate the statement.

However, I don't recommend any of those. Instead I would be concerned about a data model where you are regularly adding and modifying the columns in a table. That sounds dangerous.

Upvotes: 2

Related Questions