Oracle 11g Release 2
I'm running an UPDATE statement. In the "set" I'm trying to update a column using a subquery on the right-hand side. The REPLACE statement is getting the error ORA-01747: invalid user.table.column, table.column, or column specification.
create table tab1
( file_id number ,
record_id number ,
part_type_id number ,
part_number number ,
positon_id number ,
notes varchar2(255) ,
mapped varchar2(1) default 'N'
)
/
insert into tab1 values(1,1,123,777,1,'NEW|A/C COMPRESSOR KIT','N') ;
insert into tab1 values(1,2,123,777,2,'REMAN|A/C LINE','N') ;
insert into tab1 values(1,3,123,777,2,'TEST NOTE','N') ;
commit;
select * from tab1;
/*
FILE_ID RECORD_ID PART_TYPE_ID PART_NUMBER POSITON_ID NOTES MAPPED
--------- ---------- ------------ ----------- ---------- ------------------------- ------
1 1 123 777 1 NEW|A/C COMPRESSOR KIT N
1 2 123 777 2 REMAN|A/C LINE N
1 3 123 777 1 TEST NOTE N
*/
DECLARE
v_note2 varchar2(255) := 'NEW' ;
BEGIN
UPDATE tab1 t
SET
, t.notes = (select REPLACE(t.notes,v_note2,null)
from dual )
WHERE t.file_id = 1 -- value comes from another source
AND t.part_type_id = 123 -- value comes from another source
AND t.part_number = 777 -- value comes from another source
AND t.position_id = 1 ; -- value comes from another source
END ;
/
ERROR at line 3:
ORA-01747: invalid user.table.column, table.column, or column specification
Here is what the final output should be:
FILE_ID RECORD_ID PART_TYPE_ID PART_NUMBER POSITON_ID NOTES MAPPED
--------- ---------- ------------ ----------- ---------- ------------------------- ------
1 1 123 777 1 |A/C COMPRESSOR KIT N
1 2 123 777 2 REMAN|A/C LINE N
1 3 123 777 1 TEST NOTE N