UPDATE part of MERGE INTO problem

[GeppoDarkson]

Hi to all,
may I update a field that is part of the "ON" clause of a MERGE INTO statement?

I have two tables for temperature data: TReal And TTemp with the same structure:
IdStation, DT_Hour, Measure, Rank

I want to insert in TReal the missing records of TTemp (same IdStation & DT_Hour)
and
to Update Measure and Rank Of records with same IdStation & DT_Hour but TReal.Rank <= TTemp.Rank

The statement:

Merge into TReal R
Using (Select Id,IdSens,Dt,Valore,XXXX From TTEMP) T
ON (R.IdStation = T.IdStation and R.Dt_Hour=T.Dt_Hour And [COLOR=red]R.RANK[/color] <= T.RANK)
When Matched Then Update Set R.Measure=T.Measure , [COLOR=red]R.RANK[/color] = T.Rank
When not Matched Then Insert(IdStation Dt_Hour ,Measure,Rank) Values (T.IdStation ,T.Dt_Hour ,T.Measure,T.Rank)

Says that is an Ora-00904 error (no field) on the 3rd row at R.Rank.

It seems that this is caused by the Update clause:
When I create a new field TReal.Rank2 and upate this instead of Rank everithing works.

Merge into TReal R
Using (Select Id,IdSens,Dt,Valore,XXXX From TTEMP) T
ON (R.IdStation = T.IdStation and R.Dt_Hour=T.Dt_Hour And [COLOR=red]R.RANK[/color] <= T.RANK)
When Matched Then Update Set R.Measure=T.Measure , [COLOR=green]R.RANK2[/color] = T.Rank
When not Matched Then Insert(IdStation Dt_Hour ,Measure,Rank) Values (T.IdStation ,T.Dt_Hour ,T.Measure,T.Rank)

So, may I update a field that is part of the "ON" clause of a MERGE INTO statement?

Thank You.
Geppo Darkson.

 

[GeppoDarkson]

Sorry, the second row is:

Using (Select IdStation,Dt_Hour,Measure,Rank From TTEMP) T

I've notice also that "R.RANK <= T.RANK" in the ON clause triggers the INSERT statement for records (not matched) that haves a R.Rank higher than T.Rank, duplicating the primary key IdStation+Dt_Hour

Should I make two distinct query for insert and for update?

Thank you.

 

[karluk]

The answer to your question is "no" - it's not permitted to update columns mentioned in the ON clause of a merge into statement. Because of this limitation of the merge command, I would tend to split your operation into separate update and inserts.

 

[GeppoDarkson]

Hi to all.

I've solved this problem using this:

Merge into TReal R
Using
(Select TTemp.Id,TTemp.IdSens,TTemp.Dt,TTemp.Valore,TTemp.Rank,
              TReal.Rank As TrRank
       From TTemp Left Outer Join TReal
       On TTemp.IDSENS = TReal.IdSens
       and TReal.Dt=TTemp.Dt
       [B]Where ((TReal.Rank Is Null) OR (TTemp.Rank >= TReal.Rank)) [/B]
) T
ON (R.IDSENS = T.IdSens and R.Dt=T.Dt)
When Matched Then Update Set R.Valore=T.Valore, R.Rank = T.Rank
When not Matched Then Insert(IdSens,Dt,Valore,Rank) Values (T.IdSens,T.Dt,T.Valore,T.Rank)

It works by filtering the USING clause excluding the records with TTemp.Rank < TReal.Rank, so no updating nor inserting are performed for the unwanted records (low rank) because they are not present in the "Using" resultset.

Ciao,
Geppo Darkson