1

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

2

Your update query doesn't seem complete or it would give another error message , also it helps to make a [mre].

create table TEST_FILE_MERGE
(
  customer_num    NUMBER,
  CUST_FIRST_NAME VARCHAR2(180),
  CUST_LAST_NAME  VARCHAR2(180),
  r_id            NVARCHAR2(1000)
  ,
  CONSTRAINT cumstom_unique UNIQUE (customer_num)
);
INSERT INTO TEST_FILE_MERGE VALUES( 123,'perter','foe','1')
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
);
INSERT INTO REQUEST VALUES (1,123,'Peter','Foe',0,0)
UPDATE (SELECT A.CUST_FIRST_NAME, A.CUST_LAST_NAME
,B.CUSTOMER_FIRST_NAME,B.CUSTOMER_LAST_NAME,B.ERROR_CODE,B.IS_CHECKED  
          FROM TEST_FILE_MERGE A
         INNER JOIN REQUEST B
            ON A.CUSTOMER_NUM = B.CUST_NO)
   SET CUSTOMER_FIRST_NAME  = CUST_FIRST_NAME,
       CUSTOMER_LAST_NAME   = CUST_LAST_NAME,
       ERROR_CODE           = 0,
       IS_CHECKED           = 1;
1 rows affected
SELECT * FROM REQUEST
REQ_ID | CUST_NO | CUSTOMER_FIRST_NAME | CUSTOMER_LAST_NAME | ERROR_CODE | IS_CHECKED
:----- | :------ | :------------------ | :----------------- | ---------: | ---------:
1      | 123     | perter              | foe                |          0 |          1

db<>fiddle here db<>fiddle here

6
  • Again I receive "B"."IS_CHECKED" invalid identifier
    – Pantea
    Dec 13 '20 at 16:42
  • 1
    please make a minimal reproducible example So that i can see if IS_CHECKED exists in request your first query seemes to indicate it
    – nbk
    Dec 13 '20 at 16:48
  • I added the table's structure.
    – Pantea
    Dec 13 '20 at 17:18
  • 1
    there is something wroong dbfiddle.uk/… there is missing REQUEST_ID that looked already strange
    – nbk
    Dec 13 '20 at 17:28
  • 1
    @Pantea please take a look i made a complete example
    – nbk
    Dec 13 '20 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.