I'm trying to transform a
Merge query into an
Update query for getting better performance but I could not succeed.
create table TEST_FILE_MERGE ( customer_num NUMBER, cust_first_name VARCHAR2(180), cust_last_name VARCHAR2(180), r_id NVARCHAR2(1000) ) create table REQUEST ( req_id NVARCHAR2(1000), cust_no VARCHAR2(50), customer_first_name VARCHAR2(50), customer_last_name VARCHAR2(50), error_code NUMBER, is_checked NUMBER(1) )
Here is my Merge query:
MERGE INTO (SELECT * FROM REQUEST WHERE REQ_ID = REQUEST_ID) R USING TEST_FILE_MERGE A ON (A.CUSTOMER_NUM = R.CUST_NO) WHEN MATCHED THEN UPDATE SET R.CUSTOMER_FIRST_NAME = A.CUST_FIRST_NAME, R.CUSTOMER_LAST_NAME = A.CUST_LAST_NAME, R.ERROR_CODE = 0, R.IS_CHECKED = 1;
And Here is my Update Query which is not correct:
UPDATE (SELECT A.CUST_FIRST_NAME, A.CUST_LAST_NAME, FROM TEST_FILE_MERGE A INNER JOIN REQUEST B ON A.CUSTOMER_NUM = B.CUST_NO) SET B.CUSTOMER_FIRST_NAME = A.CUST_FIRST_NAME, B.CUSTOMER_LAST_NAME = A.CUST_LAST_NAME, B.ERROR_CODE = 0, B.IS_CHECKED = 1; COMMIT;
I receive error
ORA-00904:"B"."IS_CHECKED" invalid identifier but column
IS_CHECKED exists in
REQUEST table .
What is wrong with my update query?And how am I suppose to put this part of my merge
SELECT * FROM REQUEST WHERE REQ_ID = REQUEST_ID into my update statement?
Thanks in advance