| Re: sql query(try again) [message #375061] |
Mon, 23 July 2001 05:47 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
To find the rows that have duplicates, you could try
SELECT col1,col2,col3,col5
FROM table
HAVING count(*) >1
GROUP BY col1,col2,col3,col5;
To find the rows and their rowids, try
SELECT rowid,col1,col2,col3,col5
FROM table
WHERE (col1,col2,col3,col5) IN (
SELECT col1,col2,col3,col5
FROM table
HAVING count(*) >1
GROUP BY col1,col2,col3,col5);
To delete the rows, try:
DELETE table t1
WHERE EXISTS (SELECT 1
FROM table t2
WHERE t1.col1 = t2.col1
AND t1.col2 = t2.col2
..... (rest of column matches between the two rows)
AND t1.rowid< t2.rowid)
Hope this helps.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
