Need to delete records from FACT table using filter from Part_Dim table

[PriyadharshiniJ]

Using Oracle query, I need to delete records from fact table which has 0 value in qty fields for only particular Materials. Material information is available in only PART_DIM table. I have retrieved data set which has to be deleted using the below INNER JOIN query but am not able to delete the records.

SELECT *
FROM FACT_TABLE FACT
INNER JOIN PART_DIMENSION PART ON FACT.PART_ID = PART.PART_ID
AND FACT.OWNER_ID = PART.OWNER_ID
WHERE PART.MATERIAL_NAME IN ('VALUE1','VALUE2')
AND (FACT.Q1_QTY_TOTAL + FACT.Q2_QTY_TOTAL +FACT.Q3_QTY_TOTAL +FACT.Q4_QTY_TOTAL ) = 0;

If I replace the select with DELETE, am getting error as SQL Command not properly ended.

DELETE FACT
FROM FACT_TABLE FACT
INNER JOIN PART_DIMENSION PART ON FACT.PART_ID = PART.PART_ID
AND FACT.OWNER_ID = PART.OWNER_ID
WHERE PART.MATERIAL_NAME IN ('VALUE1','VALUE2')
AND (FACT.Q1_QTY_TOTAL + FACT.Q2_QTY_TOTAL +FACT.Q3_QTY_TOTAL +FACT.Q4_QTY_TOTAL ) = 0;

Please help me how to delete the records without PL/SQL procedures.

[Alberto Bucur]

Hi,
Try this

DELETE FROM 
    ( SELECT * FROM FACT_TABLE FACT
      INNER JOIN PART_DIMENSION PART ON FACT.PART_ID = PART.PART_ID
                AND FACT.OWNER_ID = PART.OWNER_ID
    )
    WHERE MATERIAL_NAME IN ('VALUE1','VALUE2')
    AND (Q1_QTY_TOTAL + Q2_QTY_TOTAL +Q3_QTY_TOTAL+Q4_QTY_TOTAL ) = 0;