[SOLVED] Update field value of a table with max value of two fields of two other tables

Update field value of a table with max value of two fields of two other tables

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?

Solution

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