I have a 2 tables where in table 1, i have a clob file which has a wrong value, which I need to replace it with correct value from another table.

Table1

in the above table1 N000**** is incorrect, Now I have table2 in which I have the correct value (and this is not a clob)

Table2

Now I have tried something like the below which is throwing error

update table1
set column2 = replace (column2, select column3 from table2 where table1.column1 = table2.column1,
                              select column2 from table2 where table1.column1 = table2.column1)

where table1.column1 in (select column1 from table2 where table1.column1 = table2.column1);

However oracle is throwing a missing expression error.

Can someone please help me?

CodePudding user response：

You can use a MERGE statement:

MERGE INTO table1 dst
USING (
  SELECT t1.ROWID AS rid,
         REPLACE(t1.column2, t2.column3, t2.column2) AS replacement
  FROM   table1 t1
         INNER JOIN table2 t2
         ON (t1.column1 = t2.column1 AND t1.column2 LIKE '%'||t2.column3||'%')
) src
ON (src.rid = dst.ROWID)
WHEN MATCHED THEN
  UPDATE
  SET column2 = src.replacement;

Which, for the sample data:

CREATE TABLE table1 (column1, column2) AS
SELECT 1234, EMPTY_CLOB() || RPAD('abc', 4000,  '.') || 'N0001234' FROM DUAL UNION ALL
SELECT 2345, TO_CLOB('askdfa.. N0002434....') FROM DUAL;

After the MERGE, the table contains:

COLUMN1	COLUMN2
1234	abc.............................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................N0004567
2345	askdfa.. N0002435....

db<>fiddle here