Re: SQL question; Please Help!

From: skv <skv_at_kpbank.ru>
Date: 1997/10/21
Message-ID: <01bcddf5$c59a96e0$0bc47cc1@skv.hq.kem>#1/1

	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