CODEWITHSUNDEEP
mysqlsql
AndrewBourgeois
AndrewBourgeois

Reputation: 2765

Update rows with duplicate values (in the same columns)

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.

Upvotes: 3

Views: 8792

Answers (4)

AndrewBourgeois
AndrewBourgeois

Reputation: 2765

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) ;)

Upvotes: 3

Devart
Devart

Reputation: 121922

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;

Upvotes: 7

Icarus
Icarus

Reputation: 63966

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.

Upvotes: 0

John K.
John K.

Reputation: 5474

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 
)

Upvotes: 0

Related Questions