CODEWITHSUNDEEP
mysql
awiamo
awiamo

Reputation: 23

MySQL update all values in column based on max date and group

I'm trying to use group by clause to update the dt column based on column 'last'. I need find last date for group 'group by hid,tid,tdate,fid,did,depid,acc' and set 'dt' value for all record in this group.

For example:

|       id |   hid | tid |      tdate | fid | did |    p2 | depid |  acc |   dt |                 last |
|----------|-------|-----|------------|-----|-----|-------|-------|------|------|----------------------|
|  3742030 | 12332 |   1 | 2017-09-02 |   1 |   1 |  9560 |     1 | 5334 |    1 | 2016-11-03T09:00:20Z |
|  3799297 |  2386 |   1 | 2017-08-29 |   1 |   1 |  8480 |     1 | 5352 |    1 | 2016-11-03T11:12:55Z |
|  4848877 |  2386 |   1 | 2017-08-29 |   1 |   1 |  8720 |     1 | 5352 | 2369 | 2016-12-17T16:59:22Z |
| 10706343 | 12332 |   1 | 2017-09-02 |   1 |   1 |  9660 |     1 | 5334 | 2065 | 2017-03-01T12:32:27Z |
| 14546682 |  2386 |   1 | 2017-08-29 |   1 |   1 | 11720 |     1 | 5352 | 4431 | 2017-05-12T10:24:09Z |
| 15824920 | 12332 |   1 | 2017-09-02 |   1 |   1 | 10820 |     1 | 5334 | 1111 | 2017-07-15T05:19:04Z |

to

|       id |   hid | tid |      tdate | fid | did |    p2 | depid |  acc |   dt |                 last |
|----------|-------|-----|------------|-----|-----|-------|-------|------|------|----------------------|
|  3742030 | 12332 |   1 | 2017-09-02 |   1 |   1 |  9560 |     1 | 5334 | 1111 | 2016-11-03T09:00:20Z |
|  3799297 |  2386 |   1 | 2017-08-29 |   1 |   1 |  8480 |     1 | 5352 | 4431 | 2016-11-03T11:12:55Z |
|  4848877 |  2386 |   1 | 2017-08-29 |   1 |   1 |  8720 |     1 | 5352 | 4431 | 2016-12-17T16:59:22Z |
| 10706343 | 12332 |   1 | 2017-09-02 |   1 |   1 |  9660 |     1 | 5334 | 1111 | 2017-03-01T12:32:27Z |
| 14546682 |  2386 |   1 | 2017-08-29 |   1 |   1 | 11720 |     1 | 5352 | 4431 | 2017-05-12T10:24:09Z |
| 15824920 | 12332 |   1 | 2017-09-02 |   1 |   1 | 10820 |     1 | 5334 | 1111 | 2017-07-15T05:19:04Z |

Schema: http://sqlfiddle.com/#!9/4fad1d/1

Is there some way to update all rows in the table based on group?

Thanks

Upvotes: 2

Views: 149

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31812

Join the table with the subquery that will find the most recent row per group.

update of t
join (
  select t1.*
  from of t1
  join (
    select hid,tid,tdate,fid,did,depid,acc, max(last) as last
    from of
    group by hid,tid,tdate,fid,did,depid,acc
  ) t2 using (hid,tid,tdate,fid,did,depid,acc,last)
) t3 using (hid,tid,tdate,fid,did,depid,acc)
set t.dt = t3.dt;

http://sqlfiddle.com/#!9/93708/2

For the join in the subquery you can also use NATURAL JOIN

  natural join (
    select hid,tid,tdate,fid,did,depid,acc, max(last) as last
    from of
    group by hid,tid,tdate,fid,did,depid,acc
  ) t2

http://sqlfiddle.com/#!9/e7e5ee/1

Upvotes: 1

Related Questions