Oracle-11g-R2

嘗試更新 Oracle 中的表時收到錯誤“無效標識符”

  • December 13, 2020

我正在嘗試將 aMerge query轉換為aUpdate query以獲得更好的性能,但我無法成功。

表結構:

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)
)

這是我的合併查詢:

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;

這是我不正確的更新查詢:

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;

我收到錯誤ORA-00904:"B"."IS_CHECKED" invalid identifier,但表中IS_CHECKED 存在列REQUEST

我的更新查詢有什麼問題?我應該如何將我的這部分合併SELECT * FROM REQUEST WHERE REQ_ID = REQUEST_ID到我的更新語句中?

提前致謝

您的更新查詢似乎不完整,或者它會給出另一個錯誤消息,這也有助於製作

$$ 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 行受影響
SELECT * FROM REQUEST
REQ_ID | CUST_NO | CUSTOMER_FIRST_NAME | CUSTOMER_LAST_NAME | ERROR_CODE | IS_CHECKED
:----- | :------ | :------------------ | :----------------- | ---------: | ---------:
1 | 123 | 彼得 | 敵人 | 0 | 第一的

db<>fiddle here db<>fiddle here

引用自:https://dba.stackexchange.com/questions/281453