Re: SQL question; Please Help!

Thank you for replying but my real tables are a bit more complicated. I build the sql queries dynamically.

-about 50 tables
-from 6 to 50 columns wide
-primary keys ranging from 2 to 7 columns wide

I have tried

Delete from t1 where exists (Select * from t2 where t2.a=t1.a and t2.b=t1.b)

however it does not run very fast, your example uses the exists clause as well so don't expect it to run very quickly as well. The Update statement won't be very nice on the larger tables.

skv wrote in message <01bcddf5$c59a96e0$0bc47cc1_at_skv.hq.kem>...
> hi Ivan
> mailto:skv_at_kpbank.ru
>
>Ivan Tung <ivant_at_direct.ca> ÚÁÐÉÓÁÎÏ × ÓÔÁÔØÀ
><62hjf0$49l$1_at_brie.direct.ca>...
>> I have 2 identical tables structures t1, t2.
>> I essentially want to do an insert and update on t1 from t2.
>> I plan to delete all rows in t1 that exists in t2 then insert all the
 rows
>> in t2 into t1.
>> The following delete statement works in MS SQLServer but not in Oracle!!
 ... skipped ...
>>
>> Delete t1 from t1, t2 where t1.a=t2.a and t1.b=t2.b
> DELETE t1 t1
> WHERE EXISTS (SELECT NULL
> FROM t2
> WHERE a = t1.a
> AND b = t1.b);
>> Perhaps my approach (delete, insert) is not the best solution?
>Yes.Instead of delete and insert all rows from t2 into t1 you
>may UPDATE all correlating records in t1 and then insert ONLY NEW
>records from t2 into t1:
>UPDATE t1 t1
> SET c = (SELECT c
> FROM t2
> WHERE a = t1.a
> AND b = t1.b)
> WHERE EXISTS (SELECT NULL
> FROM t2
> WHERE a = t1.a
> AND b = t1.b);
>INSERT INTO t1 (a, b, c)
>SELECT a, b, c
> FROM t2 t2
>WHERE NOT EXISTS (SELECT NULL
> FROM t1
> WHERE a = t2.a
> AND b = t2.b);
>
>Regards
>-------------------------------
> Konstantin V. Sartakov
> Kuzbassprombank
> Kemerovo
> Russia
>
>
Received on Tue Oct 21 1997 - 00:00:00 CDT