Not properly end sql query

Question

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;

Answer 1

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:

Answer 2

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.

Answer 3

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' ;