Oracle-11g-R2
嘗試更新 Oracle 中的表時收到錯誤“無效標識符”
我正在嘗試將 a
Merge 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 | 第一的