I'm trying to transform a Merge query
into an Update query
for getting better performance but I could not succeed.
Tables structure:
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
Best Answer
Your update query doesn't seem complete or it would give another error message , also it helps to make a [mre].
db<>fiddle here db<>fiddle here