CODEWITHSUNDEEP
mysqlmax
Spraty
Spraty

Reputation: 3

MYSQL Update rows with duplicate value but oldest date

Tried many suggestions to get this to work, difficult to explain so below is the data I have and the result I want to achieve.

I want to update the 'Active' Column to 0 if its not the MAX Ldate.

ID  | SNumber | Ldate      | Active
4804  188       2015-11-17   1
4806  189       2015-11-25   1
4807  190       2015-11-25   1
4808  191       2015-11-19   1
4809  192       2015-11-19   1
4820  193       2015-11-17   1
4821  193       2016-06-08   1
4830  194       2015-11-17   1
4831  194       2016-06-08   1
4828  195       2015-11-17   1
4829  195       2016-06-08   1


ID   SNumber  Ldate       Active    
4804 188      2015-11-17  1
4806 189      2015-11-25  1
4807 190      2015-11-25  1
4808 191      2015-11-19  1
4809 192      2015-11-19  1
4820 193      2015-11-17  0
4821 193      2016-06-08  1
4830 194      2015-11-17  0
4831 194      2016-06-08  1
4828 195      2015-11-17  0
4829 195      2016-06-08  1

I can get all rows with the MAX Ldate by "select ID, SNumber, Ldate from (select * from tbl order by SNumber, Ldate desc) x group by SNumber"

Thanks for taking the time to look!

Upvotes: 0

Views: 392

Answers (1)

Strawberry
Strawberry

Reputation: 33945

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,SNumber INT NOT NULL
,Ldate DATE NOT NULL
);

INSERT INTO my_table VALUES
(4804,188,'2015-11-17'),
(4806,189,'2015-11-25'),
(4807,190,'2015-11-25'),
(4808,191,'2015-11-19'),
(4809,192,'2015-11-19'),
(4820,193,'2015-11-17'),
(4821,193,'2016-06-08'),
(4830,194,'2015-11-17'),
(4831,194,'2016-06-08'),
(4828,195,'2015-11-17'),
(4829,195,'2016-06-08');

SELECT x.*
     , COALESCE(x.ldate = y.ldate,0) active 
  FROM my_table x 
  LEFT 
  JOIN 
     ( SELECT snumber
            , MAX(ldate) ldate 
         FROM my_table 
        GROUP 
           BY snumber
     ) y 
    ON y.snumber = x.snumber 
   AND y.ldate = x.ldate;

+------+---------+------------+--------+
| id   | SNumber | Ldate      | active |
+------+---------+------------+--------+
| 4804 |     188 | 2015-11-17 |      1 |
| 4806 |     189 | 2015-11-25 |      1 |
| 4807 |     190 | 2015-11-25 |      1 |
| 4808 |     191 | 2015-11-19 |      1 |
| 4809 |     192 | 2015-11-19 |      1 |
| 4820 |     193 | 2015-11-17 |      0 |
| 4821 |     193 | 2016-06-08 |      1 |
| 4830 |     194 | 2015-11-17 |      0 |
| 4831 |     194 | 2016-06-08 |      1 |
| 4828 |     195 | 2015-11-17 |      0 |
| 4829 |     195 | 2016-06-08 |      1 |
+------+---------+------------+--------+

I can't think why you'd store this, but it's easy enough to change the above to an UPDATE. It might look something like this (obviously, you'd need to alter the table design above first)...

UPDATE my_table x 
  LEFT
  JOIN 
     ( SELECT snumber
            , MAX(ldate) ldate 
         FROM my_table 
        GROUP 
           BY snumber
     ) y 
    ON y.snumber = x.snumber 
   AND y.ldate = x.ldate
   SET x.active = 0
 WHERE y.snumber IS NULL;

But I think I would normally use an INNER JOIN for an UPDATE, in which case it might look like this (perhaps bound up inside a transaction)...

 UPDATE my_table SET active = 0;
 UPDATE my_table x 
   JOIN 
      ( SELECT snumber
             , MAX(ldate) ldate 
          FROM my_table 
         GROUP 
            BY snumber
      ) y 
     ON y.snumber = x.snumber 
    AND y.ldate = x.ldate
    SET x.active = 1;

Upvotes: 2

Related Questions