0

I have a temporary table that is reloaded periodicaly. The data within this table must go to a target table (exactly the same structure), but, if I have duplicated values, it must check the last loaded data and update the target table.

Example:

TMP_DP_REGIAO

SG  NOME    LOADING_DATE
AM  RG_1102 19-FEB-14
RO  RG_1103 19-FEB-14
AP  RG_1104 19-FEB-14
TO  RG_1105 19-FEB-14
RO  RG_1106 19-FEB-14

After loaded, this data should go to TB_DP_REGIAO (that as I said, has the same structure).

The point is:

If SG in TMP already exists in TB, SQL must verify if this record in TMP has a greater LOADING_DATE than in TB. If so, update the TB. Otherwise, ignore it.

Can someone help on this issue?

Thanks in advance!

Lucas Rezende
  • 2,516
  • 8
  • 25
  • 34

1 Answers1

1

This SQL could fit your needs:

merge into TB_DP_REGIAO B
using TMP_DP_REGIAO P
on (P.SG = B.SG)
when matched then update set B.NOME = case when B.LOADING_DATE < P.LOADING_DATE then P.NOME else B.NOME end,
                             B.LOADING_DATE = case when B.LOADING_DATE < P.LOADING_DATE then P.LOADING_DATE else B.LOADING_DATE end
when not matched then insert(SG, NOME, LOADING_DATE) values(P.SG, P.NOME, P.LOADING_DATE)
;
Emmanuel
  • 13,935
  • 12
  • 50
  • 72
  • When I run it next day, when P.LOADING_DATE is one day ahead, I get a `SQL Error: ORA-30926: unable to get a stable set of rows in the source tables`. It says for me to `Remove any non-deterministic where clauses and reissue the dml.`. Do you know what is happening? The query seemed to be pretty much ok for me. – Lucas Rezende Feb 21 '14 at 13:10
  • 1
    According to this other SO post (http://stackoverflow.com/questions/2337271/ora-30926-unable-to-get-a-stable-set-of-rows-in-the-source-tables), this may be due to `TMP_DP_REGIAO` having duplicates on `SG` field (I supposed `SG` was the key). Try to check for duplicates... – Emmanuel Feb 21 '14 at 15:21