SQL question; Please Help!

From: Ivan Tung <ivant_at_direct.ca>
Date: 1997/10/20
Message-ID: <62hjf0$49l$1@brie.direct.ca>#1/1

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 deleting is where I am having trouble.

table t1, primary key(a,b)

a b c

---

1 1 a
1 2 b
1 3 c
2 1 d
2 2 e
2 3 f

table t2, primary key(a,b)

a b c

---

1 1 p
1 3 q
2 2 r

t1 after delete
a b c

---

1 2 b

2 1 d

2 3 f

The following delete statement works in MS SQLServer but not in Oracle!!

Delete t1 from t1, t2 where t1.a=t2.a and t1.b=t2.b

More Specifics:
Data wharehousing system
t1 100,000s of rows
t2 10,000s of rows
primary key from 2 to 4 fields wide (ie, char(8),int,char(8) ).

Perhaps my approach (delete, insert) is not the best solution?

I know this must be done in many applications, any suggestions are appreciated.
Thank you.

Please email me at ivan.tung_at_bchydro.bc.ca Received on Mon Oct 20 1997 - 00:00:00 CDT