Update rows with duplicate values (in the same columns)

Question

I'd like to update my table so that rows that share values (in the same columns) get tagged as such.

Example table definition:

CREATE TABLE `MyTable` (
  `id`  int NOT NULL ,
  `a`  varchar(10) NOT NULL ,
  `b`  varchar(10) NOT NULL , 
  `state`  tinyint NOT NULL , 
  PRIMARY KEY (`id`) 
) ;

I'd like to update "state" of every row that share values in the same column. So if row one has "a=AAAA" and row two has the same value for "a", both should get updated.

I tried this but it's too slow (not faster then doing it in Java using JDBC):

declare mycursor cursor for select id, a, b from mytable;
open mycursor;
repeat
   fetch mycursor into idid, aa, bb;
   update mytable set state=1 where (a=aa, b=bb)
until done end repeat;
close mycursor;

Any ideas to make this way better? I haven't done any decent SQL in years.

score 7 · Answer 1 · answered Aug 30 '11 at 13:10

Try first query to view rows with duplicates -

SELECT * FROM MyTable t1
  JOIN (
    SELECT a, b FROM MyTable
      WHERE a = 'aa' AND b = 'bb' -- additional condition
      GROUP BY a, b
      HAVING COUNT(*) > 1) t2
  ON t1.a = t2.a AND t1.b = t2.b

Try this one (based on first query) to update status field -

UPDATE MyTable t1
JOIN (
  SELECT a, b FROM MyTable
  WHERE a = 'aa' AND b = 'bb' -- additional condition
  GROUP BY a, b
  HAVING COUNT(*) > 1) t2
ON t1.a = t2.a AND t1.b = t2.b
SET t1.state = 1;

score 3 · Accepted Answer · answered Aug 30 '11 at 14:14

3

The answer to my question seems to be the following:

update mytable  as t1 inner join mytable as t2 on (t1.a=t2.a or t1.b = t2.b) and t1.id <> t2.id set t1.state=1;

Please say so if it is not (it seems to work, but it might do strange things) ;)

answered Aug 30 '11 at 14:14

AndrewBourgeois

2,634
7
41
58

score 0 · Answer 3 · answered Aug 30 '11 at 12:57

0

Try something like...

update MyTable
set state = 1 
where id in (
select id 
from MyTable t1, MyTable t2 
where t1.id <> t2.id 
and t1.a = t2.a 
)

answered Aug 30 '11 at 12:57

John K.

5,426
1
21
20

I'm using MySQL, http://stackoverflow.com/questions/45494/sql-delete-cant-specify-target-table-for-update-in-from-clause. Thank you for your effort, though!! :) – AndrewBourgeois Aug 30 '11 at 14:01

score 0 · Answer 4 · answered Aug 30 '11 at 13:03

You don't need a cursor for that, you can simply execute your update statement as you have it and all rows will be updated in one shot provided the condition holds true.

If I am missunderstanding your question and you do need to go through every record in that way, you can easily change the cursor for a simple while loop starting with min(Id) and ending with max(Id). The while loop should perform much faster than the cursor.

Update rows with duplicate values (in the same columns)

4 Answers4