CODEWITHSUNDEEP
mysql
dealer
dealer

Reputation: 474

How to update table data using group by in mysql

I got a situation where I have table with two columns(col1, col2) and I added boolean column(col3). Now I have to update the rows with distinct col1 and first occurence of col2 (i.e) same as rows we get from group by col1 with col3 as true. 

 create table my_table( col1 integer, col2 integer, col3 tinyint(1));

 insert into my_table(col1,col2) values(1,2);
 insert into my_table(col1,col2) values(1,1);
 insert into my_table(col1,col2) values(2,1);
 insert into my_table(col1,col2) values(2,2);
 insert into my_table(col1,col2) values(2,8);
 insert into my_table(col1,col2) values(3,2);
 insert into my_table(col1,col2) values(3,1);
 insert into my_table(col1,col2) values(3,5);
 insert into my_table(col1,col2) values(3,6);
 insert into my_table(col1,col2) values(4,3);
 insert into my_table(col1,col2) values(4,6);
 insert into my_table(col1,col2) values(4,5);
 insert into my_table(col1,col2) values(4,2);

When I added new column table has following values with col3 null.

 COL1   COL2    COL3
  1     2      (null)
  1     1      (null)
  2     1      (null)
  2     2      (null)
  2     8      (null)
  3     1      (null)
  3     2      (null)
  3     5      (null)
  3     6      (null)
  4     3      (null)
  4     6      (null)
  4     5      (null)
  4     2      (null)

I want a query to update my table as follows:

  COL1  COL2    COL3
  1     2        1
  1     1      (null)
  2     1        1
  2     2      (null)
  2     8      (null)
  3     1        1
  3     2      (null)
  3     5      (null)
  3     6      (null)
  4     3        1
  4     6      (null)
  4     5      (null)
  4     2      (null)

SQLFiddle schema link: http://sqlfiddle.com/#!2/0ddce/1

Upvotes: 0

Views: 182

Answers (2)

Tom
Tom

Reputation: 6663

I tested this on your sample data and it works:

UPDATE  my_table

SET     col3 = 1

WHERE   col2 = (SELECT col2
                  FROM   my_table m
                  WHERE  m.col1 = col1
                  LIMIT 1
                 );

Upvotes: 0

RonaldBarzell
RonaldBarzell

Reputation: 3830

If you don't mind getting errors in your updates, you can just define col1 as your primary key. This will ensure that if col1 exists in your table, it cannot be added again.

Upvotes: -2

Related Questions