Hi all,
I am a newbie to oracle SQL. I have a requirement like this . I need to update a column in one table from another table. Indexes are created on two columns (SALES_ORD_ID,ORD_ITEM_NBR). I need to update the TAX_LC_AMT column in TABLE A from another column in Table B. I have written the following queries to fetch it and update .
The query to retrieve records took 10 min 38 sec and the update is not at all happening. It keeps on runnning. Please provide your help on optimising the following queries. Here are the queries:
Query to identify tax_lc_amt column which is different from another column(MWSBP): This query returned half a million plus records.
SELECT V.VBELN,V.POSNR,V.MWSBP,D.ORD_ENT_DT,D.LAST_MOD_DT,D.TAX_LC_AMT FROM VBAP_AGG V
INNER JOIN DIM_SALES_ORDER D
ON (D.SALES_ORD_LKUP_ID='ERP'||'-'||Trim(V.VBELN)||'-'||Trim(V.POSNR))
WHERE (TO_CHAR(D.ORD_ENT_DT,'YYYY') ='2019' AND D.TAX_LC_AMT <> V.MWSBP)
Query to update the TAX_LC_AMT column with actual column (MWSBP)which is not matching with the actualcolumn(MWSBP):
UPDATE JDETST.BKUP_DIM_SALES_ORDER_TST D
SET TAX_LC_AMT=(SELECT V.MWSBP FROM ERPTST.VBAP_AGG V
WHERE
V.VBELN=D.SALES_ORD_ID AND V.POSNR=D.ORD_ITEM_NBR
AND D.SRC_SYS_ID='ERP'
AND TO_CHAR(D.ORD_ENT_DT,'YYYY') ='2019'AND D.TAX_LC_AMT<>V.MWSBP);
COMMIT;