working on duplicate rows

[koyott]

Can anyone tell me how can I operate with duplicate rows by sql queries?
for example, if I have table where duplicate rows are allowed, and 5 same rows in it, how can I form the where clause in update query to update only one of same 5 rows?

[Divya]

There are different ways you can handle this.
1. To get rid of them, just create a new table by:
CREATE TABLE newtab AS
SELECT DISTINCT *
FROM oldtab;
After making sure the new table looks okay, you can delete from original table and copy the data in from the new table.

2. To find wholly duplicate rows you must name ALL the columns in the table.
SELECT col1, col2, ..., colN, count(*)
FROM oldtable
GROUP BY col1, col2, ..., colN
HAVING count(*) > 1;

Once you have a table with no duplicate rows, you can go ahead with the update.
HTH

[petda03]

Another approach might be to update the table containing the duplicate rows based on tid (tuple identifier). A tid is what ingres uses to uniquely identify each row in a table.
If you want to update 1 row in a table with 5 rows you might want to do something like below:
* select * from tab1 \g
Executing . . .


+-------------+-------------+-------------+
|col1 |col2 |col3 |
+-------------+-------------+-------------+
| 1| 2| 3|
| 1| 2| 3|
| 1| 2| 3|
| 1| 2| 3|
| 1| 2| 3|
+-------------+-------------+-------------+
(5 rows)
continue
* select col1 , col2 , col3 , tid from tab1 \g
Executing . . .


+-------------+-------------+-------------+----------------------+
|col1 |col2 |col3 |tid |
+-------------+-------------+-------------+----------------------+
| 1| 2| 3| 0|
| 1| 2| 3| 1|
| 1| 2| 3| 2|
| 1| 2| 3| 3|
| 1| 2| 3| 4|
+-------------+-------------+-------------+----------------------+
(5 rows)
continue
* update tab1 set col3 = 5 where tid = 0 ;commit \g
Executing . . .

(1 row)
continue
* select col1 , col2 , col3 , tid from tab1; commit \g
Executing . . .


+-------------+-------------+-------------+----------------------+
|col1 |col2 |col3 |tid |
+-------------+-------------+-------------+----------------------+
| 1| 2| 5| 0|
| 1| 2| 3| 1|
| 1| 2| 3| 2|
| 1| 2| 3| 3|
| 1| 2| 3| 4|
+-------------+-------------+-------------+----------------------+
(5 rows)
continue

So only one - the first in the example above - is updated.

You can find more info on tids in the Ingres DBA guide.

HTH.

Dave

[stephenb]

Also check out this thread

SQL Update and search conditions