CODEWITHSUNDEEP
sqloracle-databasedatesql-update
Symonds
Symonds

Reputation: 194

Oracle Update statement not working correctly

I want to consider for every combination of field value OID and EXID take the min LATEST_MODIFICATION_DATE and update the same in FIRST_INSERTION_DATE.

I have lot of redundant rows with the combination of field value OID and EXID. But it should not be problem to set the min LATEST_MODIFICATION_DATE date for all those values.

I tried the below query but its not working correctly:

update O_TEST t
set FIRST_INSERTION_DATE = (select min (LATEST_MODIFICATION_DATE) from O_TEST)
where rowid < (
  select max(rowid)
  from OBS_ORDER_ID_MAPPING_TEST t2
  where t.EXID = t2.EXID
    and t.OID = t2.OID
  );

Upvotes: 1

Views: 355

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You need a correlated subquery for the set:

update O_TEST t
    set FIRST_INSERTION_DATE = (select min(t2.LATEST_MODIFICATION_DATE)
                                from O_TEST t2
                                where t2.EXID = t.EXID and t2.OID = t.OID
                               )
    where LATEST_MODIFICATION_DATE <> (select min(t2.LATEST_MODIFICATION_DATE)
                                       from O_TEST t2
                                       where (t2.EXID = t.EXID or t2.EXID is null and t.EXID is null) and
                                             t2.OID = t.OID
                                      );

To handle NULL values, you need to take those into account in the correlated subqueries:

update O_TEST t
    set FIRST_INSERTION_DATE = (select min(t2.LATEST_MODIFICATION_DATE)
                                from O_TEST t2
                                where t2.EXID = t.EXID and

t2.OID = t.OID ) where LATEST_MODIFICATION_DATE <> (select min(t2.LATEST_MODIFICATION_DATE) from O_TEST t2 where (t2.EXID = t.EXID or t2.EXID is null and t.EXID is null) and t2.OID = t.OID );

From your comment OID is not NULL, but if so, you can use similar logic for that as well.

Upvotes: 1

Related Questions