Why this update throws error not properly end?
UPDATE FACT_MGP_MASDP1 SET STATUS_DN = 'AGING_REPORTED', ASSET_DN_KEY = '7258-1046', STATUS = 'AGING_REPORTED' FROM FACT_MGP_MASDP1 T1 INNER JOIN FACT_MGP_ASSET T2 ON (T2.ASSET_ID = T1.ASSET_ID) WHERE T1.DATEKEY = 20171231 OR T1.DATEKEY = 20171130; Oracle does not support update using join, but we can rewrite as below query using Inline View:
UPDATE ( SELECT STATUS_DN AS OldVal, ASSET_DN_KEY AS OldVal1, STATUS AS OldVal2 FROM FACT_MGP_MASDP1 T1 INNER JOIN FACT_MGP_ASSET T2 ON (T2.ASSET_ID = T1.ASSET_ID) WHERE T1.DATEKEY = 20171231 OR T1.DATEKEY = 20171130 ) AS t SET t.OldVal = 'AGING_REPORTED', t.OldVal1= '7258-1046' , t.OldVal2= 'AGING_REPORTED' ; Below is the general syntax:
Oracle does not support update join syntax, but we can rewrite as:
UPDATE FACT_MGP_MASDP1 SET STATUS_DN = 'AGING_REPORTED', ASSET_DN_KEY = '7258-1046', STATUS = 'AGING_REPORTED' FROM FACT_MGP_MASDP1 T1 WHERE EXISTS (SELECT 1 FROM FACT_MGP_ASSET WHERE T2.ASSET_ID = T1.ASSET_ID) AND T1.DATEKEY IN (20171231, 20171130) The reason this should be logically equivalent to what you wrote in your question is that your inner join was only filtering off records from the target table which did not match to anything in the FACT_MGP_ASSET table.
Use update with the inner select query:
UPDATE ( SELECT STATUS_DN, ASSET_DN_KEY, STATUS FROM FACT_MGP_MASDP1 T1 INNER JOIN FACT_MGP_ASSET T2 ON (T2.ASSET_ID = T1.ASSET_ID) WHERE T1.DATEKEY = 20171231 OR T1.DATEKEY = 20171130 ) AS A SET A.STATUS_DN = 'AGING_REPORTED', A.ASSET_DN_KEY= '7258-1046' , A.STATUS= 'AGING_REPORTED' ; 