Here's what Joe Celko writes in "SQL For Smarties" about removing
duplicate rows from a table.
* You get them when you don't put a UNIQUE constraint on the table and
then you insert the same data twice.
* Removing the extra copies is harder than you may think, because you
want to retain one while deleting the rest.
* The job is tougher if there are referential integrity constraints on
the table.
Here's one method, usable if the table has no references to other tables
or if any such references can be temporarily dropped:
INSERT INTO WorkingTable
SELECT DISTINCT * FROM MessedUpTable;
DELETE FROM MessedUpTable;
INSERT INTO MessedUpTable
SELECT * FROM WorkingTable;
DROP TABLE WorkingTable;
Here's the classic method for Oracle:
DELETE FROM MessedUpTable
WHERE ROWID < (SELECT MAX(M1.ROWID)
FROM MessedUpTable AS M1
WHERE M1.id = MessedUpTable.id
AND M1.name = MessedUpTable.name
AND ...);
where the fields of MessedUpTable are id, name, ...
-David
________________________________________________________________
YOU'RE PAYING TOO MUCH FOR THE INTERNET!
Juno now offers FREE Internet Access!
Try it today - there's no risk! For your FREE software, visit:
http://dl.www.juno.com/get/tagj.
------------------------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
