Hari wrote:I supposed to edit my previous post, mean time you already posted new.
Thank for your reply. i am specifying alias name for it. other wise it will through error message.
ORA-00904: "ACC"."ACCOUNT_ID": invalid identifier
SQL> UPDATE ACCOUNT ACC SET ACC.LAST_SUCC_ACQ_TS = ( SELECT ACQ_START_TS FROM BANKING_STMT_TASK BST WHERE BST.STMT_ID IN ( SELECT LAST_SUCC_BANKING_STMT_ID FROM BANKING_ACCOUNT_DETAIL BNK WHERE BNK.ACCOUNT_ID=ACC.ACCOUNT_ID ) );
As you can see, you don't need to use alias after SET, it is used only in select clause.
UPDATE employees a SET department_id = (SELECT department_id FROM departments WHERE location_id = '2100'), (salary, commission_pct) = (SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct) FROM employees b WHERE a.department_id = b.department_id) WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700);
Hari wrote:ok, let's check availability of tables.
i Have tried without alias after SETand getting the same error.
SQL> UPDATE ACCOUNT ACC SET LAST_SUCC_ACQ_TS =
2 ( SELECT ACQ_START_TS
FROM BANKING_STMT_TASK BST
3 4 WHERE BST.STMT_ID IN (
5 6 FROM BANKING_ACCOUNT_DETAIL BNK
7 WHERE BNK.ACCOUNT_ID=ACC.ACCOUNT_ID ));
UPDATE ACCOUNT ACC SET LAST_SUCC_ACQ_TS =
ERROR at line 1:
ORA-00942: table or view does not exist
SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME IN ('ACCOUNT','BANKING_STMT_TASK','BANKING_ACCOUNT_DETAIL')