CODEWITHSUNDEEP
sqloracle-databaseplsql
Fede E.
Fede E.

Reputation: 1918

CREATE AS SELECT * but with one column obtained from another table

I need to 'recreate' over 50 tables (in Oracle) with CREATE AS SELECT statements. However, all this tables will have one column modified using data from another table. Is there a way to achieve this without declaring each column in the SELECT statement?

Something like:

CREATE TABLE table_name_copy AS SELECT *, (SELECT col_name FROM other_table WHERE other_table.col_id = table_name.col_id) AS col_name FROM table_name`

Basically on all tables I have a column which needs to be replaced with the data in the other_table column.

Upvotes: 2

Views: 1132

Answers (4)

DDS
DDS

Reputation: 2479

I would try this (but I don't have Oracle SQL to test on so please leave me the benefit of the doubt)

CREATE TABLE table_name_copy AS 

SELECT * FROM (
               SELECT *, (SELECT col_name FROM other_table WHERE other_table.col_id = table_name.col_id) as col_name 

               FROM table_name`
               )

edit: then run

ALTER TABLE table_name_copy DROP COLUMN <old column>
to remove the column you don't need any more

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65228

If col_id column is fixed for both of the joined tables,

you may use user_tab_columns and user_tables dictionary views through the schema to produce new tables named as "table_name_copy" by using the following mechanism :

declare
 v_ddl varchar2(4000); 
 v_cln varchar2(400); 
begin
 for c in ( select * 
              from user_tables t 
             where t.table_name in
             ( select c.table_name 
                 from user_tab_columns c
                where c.column_name = 'COL_ID'  ) 
             order by t.table_name )
 loop    
   v_ddl := 'create table '||c.table_name||'_copy as 
                      select ';
   for d in ( select listagg('t1.'||column_name, ',') within group ( order by column_name ) cln 
                from user_tab_columns 
               where table_name = c.table_name 
                 and column_name != 'COL_ID' )                   
   loop
    v_cln := v_cln||d.cln;  
   end loop;
       v_ddl := v_ddl||v_cln;                       
       v_ddl := v_ddl||', t2.col_id t2_id 
        from '||c.table_name||' t1 
        left outer join other_table t2 on ( t1.col_id = t2.col_id )';

       execute immediate v_ddl;                    
       v_ddl := null;
       v_cln := null;
 end loop;
end;

Upvotes: 1

Henrique Rotava
Henrique Rotava

Reputation: 801

Maybe you can use a simple join and an asterisk to return all columns from the first table, like that:

CREATE TABLE table_name_copy AS 
  SELECT * FROM (
    SELECT tab1.*, tab2.column_name 
    FROM table_name tab1 LEFT JOIN other_table tab2 ON tab1.col_id = tab2.col_id
  );

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 220877

Generate the SQL string as such:

SELECT 'CREATE TABLE table_name_copy AS SELECT '
    || LISTAGG (column_name, ', ') WITHIN GROUP (ORDER BY column_name)
    || ', (SELECT col_name FROM other_table 
           WHERE other_table.col_id = table_name.col_id) AS col_name'
    || ' FROM table_name'
FROM all_tab_cols
WHERE owner = 'OWNER'
AND table_name = 'TABLE_NAME'
AND column_name != 'COL_NAME'

If you want to run the above statement, you could use EXECUTE IMMEDIATE:

DECLARE
  v_sql VARCHAR2(10000);
BEGIN
  SELECT 'CREATE TABLE table_name_copy AS SELECT '
      || LISTAGG (column_name, ', ') WITHIN GROUP (ORDER BY column_name)
      || ', (SELECT col_name FROM other_table 
             WHERE other_table.col_id = table_name.col_id) AS col_name'
      || ' FROM table_name'
  INTO v_sql
  FROM all_tab_cols
  WHERE owner = 'OWNER'
  AND table_name = 'TABLE_NAME'
  AND column_name != 'COL_NAME';

  EXECUTE IMMEDIATE v_sql;
END;
/

Upvotes: 2

Related Questions