Update value from a select statement

Question

Hi there,

I'm using an Access over Oracle database system (Basically using Access for the forms and getting into the tables using ADO code) and am trying to update a field in the product table with the value of the same named field in a load table. The code I am using is:

.CommandText = "UPDATE " & strSchema & ".TBL_CAPITAL_MGMT_PRODUCT a INNER JOIN " & strSchema & ".TBL_CAPITAL_MGMT_TEMP_LOAD b ON a.AR_ID = b.AR_ID SET a.TOT_RWA_AMT = b.TOT_RWA_AMT;"

Which returns an error about missing SET keyword.. So I changed it to:

.CommandText = "UPDATE (SELECT a.TOT_RWA_AMT, b.TOT_RWA_AMT As New_RWA_AMT FROM " & strSchema & ".TBL_CAPITAL_MGMT_TEMP_LOAD a INNER JOIN " & strSchema & ".TBL_CAPITAL_MGMT_PRODUCT b ON b.AR_ID = a.AR_ID Where a.New_Rec <> '-1' AND a.IP_ID Is Not Null) c SET c.New_RWA_AMT = c.TOT_RWA_AMT;"

Which returns an error about non key-preserved table. the b table has a pk of AR_ID but the a table has no primary key and it probably won't be getting one, I can't update the structure of any of the tables.

I tried using the /*+ BYPASS_UJVC */ which lets the code run, but doesn't actually seem to do anything.

Anyone got any ideas where I should go from here?

Thanks

Alex

Answer 1

Ignoring the irrelevant ADO code, the update you are trying to do is:

UPDATE TBL_CAPITAL_MGMT_PRODUCT a 
INNER JOIN 
SET a.TOT_RWA_AMT = b.TOT_RWA_AMT;

This isn't supported by Oracle (though maybe this undocumented BYPASS_UJVC hint is supposed to overcome that, but I wasn't aware of it till now).

Given that your inline view version fails due to lack of constraints you may have to fall back on the traditional Oracle approach using correlated subqueries:

UPDATE TBL_CAPITAL_MGMT_PRODUCT a 
SET a.TOT_RWA_AMT = (SELECT b.TOT_RWA_AMT 
                       FROM TBL_CAPITAL_MGMT_TEMP_LOAD b
                      WHERE a.AR_ID = b.AR_ID
                    )
WHERE EXISTS (SELECT NULL 
                FROM TBL_CAPITAL_MGMT_TEMP_LOAD b
               WHERE a.AR_ID = b.AR_ID
             );    

The final WHERE clause is to prevent TOT_RWA_AMT being set to NULL on any "a" rows that don't have a matching "b" row. If you know that can never happen you can remove the WHERE clause.

Answer 2

If you're using Oracle 10g or higher, an alternative to Tony's solution would be to use a MERGE statement with only a MATCHED clause.

MERGE INTO TBL_CAPITAL_MGMT_PRODUCT a
   USING TBL_CAPITAL_MGMT_TEMP_LOAD b
   ON (a.AR_ID = b.AR_ID)
WHEN MATCHED THEN
   UPDATE SET a.TOT_RWA_AMT = b.TOT_RWA_AMT;