I have this select statement where I found two rows, and I want to update a specific column:
select * from murex.STPDLV_ENTRY_TABLE a inner join (select DLV_FLOW_XX, max(TS_TIME_LONG) AS MaxDateTime from murex.STPDLV_ENTRY_TABLE group by DLV_FLOW_XX) b on a.DLV_FLOW_XX = b.DLV_FLOW_XX and a.TS_TIME_LONG=b.MaxDateTime and a.DLV_FLOW_XX in (3741539,4044126,3741551) and a.STP_UDF9 !='Posted';
I have tried:
update STPDLV_ENTRY_TABLE a set a.STP_UDF9 = 'Posted' where inner join (select DLV_FLOW_XX, max(TS_TIME_LONG) AS MaxDateTime from murex.STPDLV_ENTRY_TABLE group by DLV_FLOW_XX) b on a.DLV_FLOW_XX = b.DLV_FLOW_XX and a.TS_TIME_LONG=b.MaxDateTime and a.DLV_FLOW_XX in (3741539,4044126,3741551);
How do I correctly construct the update query?
SOLUTION:
update (select * from STPDLV_ENTRY_TABLE a inner join (select DLV_FLOW_XX , max(TS_TIME_LONG) AS MaxDateTime from STPDLV_ENTRY_TABLE group by DLV_FLOW_XX) b on a.DLV_FLOW_XX = b.DLV_FLOW_XX and a.TS_TIME_LONG = b.MaxDateTime and a.DLV_FLOW_XX in (3741539,4044126,3741551) and a.STP_UDF9 !='Posted') test set test.STP_UDF9 = 'Posted';
Advertisement
Answer
Just put your query as a table:
update (select * from STPDLV_ENTRY_TABLE a inner join (select DLV_FLOW_XX , max(TS_TIME_LONG) AS MaxDateTime from STPDLV_ENTRY_TABLE group by DLV_FLOW_XX) b on a.DLV_FLOW_XX = b.DLV_FLOW_XX and a.TS_TIME_LONG = b.MaxDateTime and a.DLV_FLOW_XX in (3741539,4044126,3741551) and a.STP_UDF9 !='Posted') test set test.STP_UDF9 = 'Posted';
Here is a demo: