CODEWITHSUNDEEP
mysqlsqlsql-updateinner-query
Kavinda Jayakody
Kavinda Jayakody

Reputation: 786

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:-

Any idea of how to do this?

Upvotes: 0

Views: 213

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133380

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

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562701

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.

Upvotes: 0

Related Questions