I have this select statement where I found two rows, and I want to update a specific column:
x
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: