Correlated update query to update same column value

[Milind Pa]

I have a Reports table and want to update a column (i.e. definition column) in it depending upon a value on the same column and at the same time query should run on Oracle database as well. I have tried following query, but its giving error,

UPDATE Reports AS Rep1 INNER JOIN Reports AS Rep2 ON Rep1.id = Rep2.id
set Rep1.definition = select concat('/', 'Context2','.', substr(Rep2.definition, (instr(Rep2.definition, 'Context1')+length('Context1')+1)))

and this one also giving error,

update Reports Rep1 set Rep1.definition = (select concat('/', 'Context2','.', substr(Rep2.definition,instr(Rep2.definition, 'Context1')+length('Context1')+1)) from Reports Rep2 where Rep1.id = Rep2.id)

Can anybody assist me to have a proper query?

Regards,
Milind Pa



Edited 1 time(s). Last edit at 05/30/2011 08:33AM by Milind Pa.