Re: Development question?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 20 May 1999 12:48:01 GMT
Message-ID: <37460396.6487037@newshost.us.oracle.com>

A copy of this was sent to mdneal_at_my-dejanews.com (if that email address didn't require changing) On Wed, 19 May 1999 20:10:40 GMT, you wrote:

>Looking for some development suggestions. I need to merge multiple
>tables containing customer-supplied data into a single table for
>display on a web front end.
>
>I want to merge the tables together into a single table but want to do
>inserts only if the record doesn't exist, and updates only if there is
>a change to the data. Don't want to re-populate the table each time I
>do a merge which would occur nightly. Any development suggestions
>would be appreciated. I am not a developer but I am helping out on
>a "hot fire" project. I am from the DBA world.
>

Here is how to do that. You can use a single UPDATE/INSERT pair of statements. Assume 'new_data' represents one of your customer supplied data tables. merged_data is the single table. It might look like:

SQL> create table new_data( x      int primary key,
  2                         data   varchar2(255) );

Table created.

SQL> create table merged_data( y        int primary key,
  2                            mdata varchar2(255) );

Table created.

SQL> insert into new_data values ( 1, 'First Go Around' );
SQL> insert into new_data values ( 2, NULL );
SQL> insert into new_data values ( 3, NULL );

SQL> -- works as long as chr(0) is not a valid value for data!!
SQL> --
SQL> -- can use:
SQL> -- ( ( x <> y ) OR
SQL> --   ( x is not null and y is null ) OR
SQL> --   ( x is null and y is not null ))
SQL> --
SQL> -- instead of nvl...

SQL>
SQL> update
  2 ( select *

  3        from new_data, merged_data
  4       where new_data.x = merged_data.y
  5         and nvl(new_data.data,chr(0)) <> nvl(merged_data.mdata,chr(0))

  6 )
  7 set mdata = data
  8 /

0 rows updated.

SQL>
SQL> insert into merged_data
  2 select *
  3 from new_data
  4 where not exists ( select NULL

  5                        from merged_data
  6                       where merged_data.y = new_data.x )

  7 /

3 rows created.

SQL> select * from merged_data
  2 /

         Y MDATA

---------- ----------------------------------------
         1 First Go Around
         2
         3

SQL> insert into new_data values ( 4, 'Second Go Around' );

SQL> update new_data set data = NULL where x = 1; SQL> update new_data set data = 'Updated from NULL' where x = 2;

SQL> update
  2 ( select *

  3        from new_data, merged_data
  4       where new_data.x = merged_data.y
  5         and nvl(new_data.data,chr(0)) <> nvl(merged_data.mdata,chr(0))

  6 )
  7 set mdata = data
  8 /
2 rows updated.

SQL> insert into merged_data
  2 select *
  3 from new_data
  4 where not exists ( select NULL

  5                        from merged_data
  6                       where merged_data.y = new_data.x )

  7 /

1 row created.

SQL> select * from merged_data
  2 /

         Y MDATA

---------- ----------------------------------------
         1
         2 Updated from NULL
         3
         4 Second Go Around

>Thanks in advance,
>
>Mike Neal
>
>
>--== Sent via Deja.com http://www.deja.com/ ==--
>---Share what you know. Learn what you don't.---

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu May 20 1999 - 07:48:01 CDT