On 22 Nov 2004 02:18:39 -0800, Ciar?n wrote:
Hi,
I'm just looking for some general advice on how to approach something.
I have two tables A and B, containg common fields of product, sales
date and qtyo.
Some records contain the same data and I want to dump them in a new
table called C and leave Tables A and B containg only data that
doesn'r match.
How do i go about approaching this??
Regards,
Ciarán
Hi Ciarán,
INSERT INTO C
SELECT A.Column1, A.Column2, ..., A.ColumnN
FROM A
INNER JOIN B
ON B.Column1 = A.Column1
AND B.Column2 = A.Column2
........
AND B.ColumnN = A.ColumnN
DELETE FROM A
WHERE EXISTS (SELECT *
FROM C
WHERE C.Column1 = A.Column1
AND C.Column2 = A.Column1
.........
AND C.ColumnN = A.ColumnN)
DELETE FROM B
WHERE EXISTS (SELECT *
FROM C
WHERE C.Column1 = B.Column1
AND C.Column2 = B.Column1
.........
AND C.ColumnN = B.ColumnN)
Enclose this all in a transaction, add error handling and you're set.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
