How to dynamically generate the update statment?

[zxmgh]

Gurus,

I have a question about dynamically generate the update statment:

I have two tables, both tables have the exact same columns (and primary key), we want to achieve the following target:

some program will insert rows into the first table, only some of the columns has not null values, I need to have a stored procedure to update the second table based on which columns of the first table has not null values, for example,

if the following columns are not null:
table1.id (primary key)
table1.col2
table1.col4

The update statement should be:

update table2 set
table2.col2=table1.col2
table2.col4=table1.col4
where table2.id=table1.id

if the following columns are not null:
table1.id (primary key)
table1.col2
table1.col4
table1.col6

The update statement should be:

update table2 set
table2.col2=table1.col2
table2.col4=table1.col4
table2.col6=table1.col6
where table2.id=table1.id

Thank you very much!!

[tabreaz]

create or replace trigger trig_name
after insert, update on tabname1
for each row
declare
sqlstr varchar2(400);
valstr varchar2(400);
isfcol boolean := true;
begin
sqlstr := 'INSERT INTO tabname2(';
if col1 is not null then
sqlstr := sqlstr || col1;
valstr := ' values(''' || col1 || ''''; --3 ' if col is a string type else single '
elsif col2 is not null then
sqlstr := sqlstr || ',' || col2;
valstr := ' ,' || col2;
/* and rest of the code */
end if;
sqlstr := sqlstr || valstr;
execute immediate sqlstr;
end;

[zxmgh]

tabreaz, thanks for reply.

But I need to update table2.

By the way, we don't want to use trigger because other applications are using table1 too and we don't want to affect the functionality of other applications.

Thanks

[tabreaz]

Rather than trigger, u can implement the same concept thru a subprogram.

All the best

[John Spencer]

Simplicity is a virtue, and the simplest approach is:

CREATE PROCEDURE insert_tab1 (p_id IN NUMBER, 
                              p_col1 IN VARCHAR2,
                              p_col2 IN VARCHAR2,
                              p_col3 IN VARCHAR2,
                              p_col4 IN VARCHAR2,
                              p_col5 IN VARCHAR2,
                              p_col6 IN VARCHAR2) AS
BEGIN
   INSERT INTO table1 VALUES (p_id, p_col1, p_col2, p_col3, p_col4, 
                              p_col5, p_col6);
   IF p_col2 IS NOT NULL and
      p_col4 IS NOT NULL and
      p_col6 IS NOT NULL THEN
      
      UPDATE table2
      SET col2 = p_col2,
          col4 = p_col4,
          col6 = p_col6
      WHERE id = p_id;
   ELSIF p_col2 IS NOT NULL and
         p_col4 IS NOT NULL THEN
         
      UPDATE table2
      SET col2 = p_col2,
          col4 = p_col4
      WHERE id = p_id;
   ELSE
      NULL;
   END IF;
   COMMIT;
END;

TTFN
John

[zxmgh]

John, thanks for your reply, I have two concerns:

1. The insert into table1 is done by a third party c++ program, we can't modify it.

2. That table has about 40 columns, if I follow your way, the stored procedure will be very big, besides, normally we will insert about 3000 rows into table1, which means we will call this procedure 3000 times, is it efficient?

Right now I try to use the following way:

1. Declare a table type type1 which has the same structure as table1
2. Bulk insert into type1 from table1
3. loop through each column of type1, dynamically create the update table2 statement.
4. execute immediate the update statement

Is it possible that we can use a single UPDATE statement with CASE to handle this?

Thanks

[tabreaz]

Is it possible that we can use a single UPDATE statement with CASE to handle this

Can u ellobrate that?

[zxmgh]

Something like:

update table2 set
table2.col2= (case table1.col2 is null then table2.col2 else table1.col2)
table2.col3=(case table1.col3 is null then table2.col3 else table1.col3)
table2.col4=(case table1.col4 is null then table2.col4 else table1.col4)
...
where table2.id=table1.id

[tabreaz]

U can't do such but u can use dynamic query to generate the SQL statement

for example

if table1.col1 is not null then
sqlstr := sqlstr || 'table2.col1 = ' || table1.col1 ||chr(10);
elsif
--
--
--
end if;