Skip to Main Content
  • Questions

  • Need solution for, No of columns in the update statement are dynamic

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, Anand.

Asked: July 22, 2018 - 12:46 pm UTC

Last updated: July 24, 2018 - 10:55 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi Tom,

I want to write an UPDATE statement in which the number of columns may vary for each run based on the user choice.
Eg: DDL

CREATE TABLE "XX_MASK_REF" 
   ( "TABLE_NAME" VARCHAR2(150 BYTE), 
 "COLUMN_NAME" VARCHAR2(150 BYTE), 
 "FLAG" VARCHAR2(2 CHAR) DEFAULT 'N'
   );


A predefined set of table names and the respective column names are inserted into this table. The default value for the FLAG is set to 'N'.The user depends on his requirement will set FLAG to 'Y' which will vary for each run.

Sample data: Run 1


Table_Name Column_Name    Flag
        T1       C1    Y
        T1       C2    Y
        T1       C3    N
        T2       C1    N
        T2       C2    Y


Sample data: Run 2

Table_Name Column_Name    Flag
      T1        C1    Y
      T1        C2    N
      T1        C3    N
      T2        C1    N
      T2        C2    Y



I need an UPDATE statement which should run only for those columns to which the FLAG is set to Y group by table_name. From the dataset given above, In the Run 1, the No of columns for table T1 is 2 whereas in the Run 2 it is only one column that needs to be updated and so on for the remaining tables.

Please advise



and Connor said...

Not sure what you are really after here - but here's how you could generate the update SQL based on the metadata

SQL> CREATE TABLE XX_MASK_REF
  2     ( "TABLE_NAME" VARCHAR2(150 BYTE),
  3   "COLUMN_NAME" VARCHAR2(150 BYTE),
  4   "FLAG" VARCHAR2(2 CHAR) DEFAULT 'N'
  5     );

Table created.

SQL>
SQL> insert into XX_MASK_REF values('T1','C1','Y');

1 row created.

SQL> insert into XX_MASK_REF values('T1','C2','Y');

1 row created.

SQL> insert into XX_MASK_REF values('T1','C3','N');

1 row created.

SQL> insert into XX_MASK_REF values('T2','C1','N');

1 row created.

SQL> insert into XX_MASK_REF values('T2','C2','Y');

1 row created.

SQL>
SQL> set serverout on
SQL> declare
  2    l_sql varchar2(1000);
  3  begin
  4    for i in (
  5      select t.*,
  6              row_number() over ( partition by table_name order by column_name ) r,
  7              count(*) over ( partition by table_name ) c
  8      from   XX_MASK_REF t
  9      where flag = 'Y'
 10      order by 1,2
 11      )
 12    loop
 13      if i.r = 1 then
 14        l_sql := 'update '||i.table_name||' set '||i.column_name||' = :b'||i.r;
 15      end if;
 16      if i.r = i.c then
 17        if i.c > 1 then
 18          l_sql := l_sql || ','||i.column_name||' = :b'||i.r;
 19        end if;
 20        dbms_output.put_line(l_sql);
 21      else
 22        l_sql := l_sql || ','||i.column_name||' = :b'||i.r;
 23      end if;
 24    end loop;
 25  end;
 26  /
update T1 set C1 = :b1,C1 = :b1,C2 = :b2
update T2 set C2 = :b1

PL/SQL procedure successfully completed.



Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library