Re: INSERT, UPDATE

Well, given that your primary key is the whole table you'll never get any updates will you? Assuming you're updating via the primary key there's nothing to update. They must all be inserts surely?

However, I undertand what youpre asking and so try somthing along the lines of...

UPDATE Table1 t1
set (t1.col1, t1.col2,...,t1.coln)

• ( select t2.col1,t2.col2,...,t2.coln from Table2 t2 where t1.PKcol1 = t2.pkcol1 and t1.pkcol2 = t2.pkcol2 and .... and ( t1.col1 != t2.col1 OR t1.col2 != t2.col2 OR .... ) )

You basically need to join both tables in the select on the set of primary key columns but only find those records in the FROM table where at least one of the 'data' columns have changed.

Of course, if you;re not date stamping the updates it might well be quicker (and easier) to update all by removing the OR clauses. You decide if it matters to updates records to themselves.

--
Alan D. Mills

Jean-Yves LE STRAT wrote in message <3662BB2F.A827749E_at_comx.fr>...
>Somebody could show me how to INSERT new rows from tab2 to tab1
>and UPDATE rows of tab1 with new values from tab2
>
>Every day I replace values in tab2 with sql-loader (REPLACE option)
>( tab2 is my "temporary table" )
>
>structure of tab1 and tab 2 are identical :
>ch1 VARCHAR2(5)
>ch2 VARCHAR2(5)
>ch3 VARCHAR2(10)
>ch4 VARCHAR2(3)
>
>the primary key is (ch1, ch2, ch3, ch4)
>
>i'd like to insert new rows into tab1 and
>i'd like to update rows with new values
>
>
>to INSERT i found :
>-------------------
>
>INSERT INTO tab1 ( ch1, ch2, ch3, ch4 )
> SELECT ch1, ch2, ch3, ch4 FROM tab2
> WHERE NOT

TS(
> SELECT ch1, ch2, ch3, ch4 FROM tab1
> WHERE t1.ch1 = t2.ch1
> AND t1.ch2 = t2.ch2
> AND t1.ch3 = t2.ch3
> AND t1.ch4 = t2.ch4 );
>
>But to UPDATE i don't known well ... tha
nks to help me
>
>____________________________________________________________________
>
> Jean-Yves LE STRAT
> mailto:jylestrat_at_comx.fr
>____________________________________________________________________
Received on Mon Nov 30 1998 - 10:15:06 CST