0

I have some data tables with the below schema

DeviceStatuses Table
id, Last_Comm, Device_Id
00001, 2020-10-23, DEV1
00002, 2020-09-23, DEV2

RcptStatuses Table
id, Last_Comm, Source
R0001, 2020-10-25, DEV1
R0002, 2020-09-25, DEV2
R0003, 2020-10-30, DEV1

ReceivedTrans Table
id, Last_Comm, Source
R0001, 2020-10-25, DEV1
R0002, 2020-09-25, DEV2
R0003, 2020-10-31, DEV1

I need to update "DeviceStatuses" Table's "Last_Comm" field value with whatever the greatest(max) value in the "RcptStatuses Table's Last_Comm" field value and "ReceivedTrans Table's Last_Comm" field value. Due to some limitations, i have to use a single query to do this.

These are the expected output

DeviceStatuses Table (After update)
id, Last_Comm, Device_Id
00001, 2020-10-31, DEV1 (max value for DEV1 Last_Comm from RcptStatus and RecievedTx Table)
00002, 2020-09-25, DEV2 (max value for DEV2 Last_Comm from RcptStatus and RecievedTx Table)

And i tried this

UPDATE DeviceStatuses SET Last_Comm = 
(SELECT MAX(lst) FROM (SELECT rsns.Last_Commu AS lst FROM RcptStatuses rsns , DeviceStatuses WHERE Device_Id = rsns.Source 
UNION ALL 
SELECT rtns.Last_Comm AS lst FROM ReceivedTrans rtns, DeviceStatuses WHERE Device_Id = rtns.Source ) As T) 
WHERE 
(SELECT MAX(lst) FROM (SELECT rsns.Last_Comm AS lst FROM RcptStatuses rsns, DeviceStatuses WHERE Device_Id = rsns.Source 
UNION ALL 
SELECT rtns.Last_Comm AS lst FROM ReceivedTrans rtns , DeviceStatuses WHERE Device_Id = rtns.Source ) AS T ) > Last_Comm

But that leads to update a same time (lastCom of device 001) to all devices.

Other things to consider:-

  • DeviceId and Source are not unique (may repeat in a table)
  • Updation of the DeviceId is only needed if the DeviceId value of the DeviceStatuses table is Less than Max value of other table's or if the DeviceId field value is NULL
  • Database driver is MySQL

Any idea of how to do this?

Kavinda Jayakody
  • 705
  • 1
  • 13
  • 25

2 Answers2

1

Is not clear which column you want updated (last_comm or device_ID ) anyway if you want update last_comm for corresponding device_id you could try using an Update based on join for the max result

UPDATE DeviceStatuses d
INNER JOIN  (
    select source, max(Last_Comm ) max_last_comm
    from (
    select source, Last_Comm
    from  RcptStatuses
    UNION 
    select source, Last_Comm
    from  ReceivedTrans
    ) t
    group by source 
) t2 ON d.Device_Id = t2.source
SET d.Last_Comm =  t2.max_last_comm
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Hey Thanks man. This works. Actually i had to do some editing to make it work to my scenario. But, one problem came up. I'm using mysql and this error pops up – Kavinda Jayakody Jul 22 '20 at 19:19
  • Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'Source' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – Kavinda Jayakody Jul 22 '20 at 19:19
  • Any idea? This worked on maria DB. But in our system we have to use MySQL, and i can't disable only_full_group_by mode :/. – Kavinda Jayakody Jul 22 '20 at 19:20
  • @KavindaJayakody .. answer updated (missed union in subquery) .. the code in my sample is ok for full_group_by_mode so .. ..eventually update your question and add the code you are actually using – ScaisEdge Jul 22 '20 at 19:40
  • yeah i actually added GROUP BY and MAX aggregator to inner queries as well (it's not actually needed), and that's why that error pops up. Now it's fixed. Thanks a lot for the help man :). I'll mark this as the correct answer – Kavinda Jayakody Jul 23 '20 at 09:09
0

You don't need to get the MAX(Last_Comm) from the other tables. Just join to every row in the other tables, and bump up the Last_Comm to the greatest value among them. It'll do this row by row, but by the end, the DeviceStatuses.Last_Comm will have the greatest value.

UPDATE DeviceStatuses AS d
JOIN RcptStatuses AS rs ON d.Device_ID = rs.Source
JOIN ReceivedTrans AS rt ON d.Device_ID = rt.Source
SET d.Last_Comm = GREATEST(d.Last_Comm, rs.Last_Comm, rt.Last_Comm)

But if DeviceStatuses.Device_ID is NULL, I don't know how you expect to match it to any rows in the other tables.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • The thing is we have NULL values in our dataset. For using GREATEST is not possible. Thanks for the suggestion btw @Bill – Kavinda Jayakody Jul 22 '20 at 17:02
  • Please update your question with an example of what you want the result of the update to be (as scaisEdge requested in a comment). Include an example of what you want the result to be for a row where Device_ID is NULL. – Bill Karwin Jul 22 '20 at 17:06