MySQL update all values in column based on max date and group -
i'm trying use group clause update dt column based on column 'last'. need find last date group 'group hid,tid,tdate,fid,did,depid,acc' , set 'dt' value record in 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 way update rows in table based on group?
thanks
join table subquery find recent row per group.
update of t join ( select t1.* of t1 join ( select hid,tid,tdate,fid,did,depid,acc, max(last) last of group 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 join in subquery can use natural join
natural join ( select hid,tid,tdate,fid,did,depid,acc, max(last) last of group hid,tid,tdate,fid,did,depid,acc ) t2
Comments
Post a Comment