Skip to content
Advertisement

How update row with inner joins

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:

DEMO

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement