update TAB1 set COL1 = (select distinct COL2 from TAB2@b8 where COL3=COL4 ) where COL5 like '200512' ;(b8 is a dblink from 9i to 8i.) But the same SQL in a PL/SQL block fails:
begin update TAB1 set COL1 = (select distinct COL2 from TAB2@b8 where COL3=COL4 ) where COL5 like '200512' ; end;I get "ORA-00904 : COL4 : identificateur non valide" (COL4 is a column of TAB2, which is in the 8i instance). What can I do?
SELECT distinct COL2 INTO col2_value FROM tab2@b8 WHERE col3=col4;Now that you have stored the value, you can use it in the update statement:
UPDATE tab1 SET col1=col2_value WHERE col5 LIKE '200512';The first SELECT requires that only one row is returned, but then so does your original UPDATE statement.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.