Replace function doesn't work when I cross reference 2 tables

[bonjourno]

I would really appreciate help with this one

I want to update records in Table1 using REPLACE, only if they meet certain conditionS in TABLE1 and in TABLE2.
The regular update using just the SET statement works fine, but when I use the REPLACE statement I get errors.

This is my code:

UPDATE TABLE1
SET TEXT_VALUE = REPLACE(TEXT_VALUE, 'VALUE1', 'VALUE2')
from TABLE1 O JOIN TABLE2 L ON O.OBJECT_ID=L.OBJECT_ID
WHERE O.TEXT_VALUE like 'CONDITION1' AND L.CHECK_INOUT = 'CONDITION2';

I get the following error:
Error at Command Line : 3 Column : 1
Error report -
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:

Many Thanks,
Elias

 

[SQLBill]

Are you running this in Oracle or Microsoft SQL Server (which is what this forum is for)? It seems to be Oracle from the error message: ORA-00933.

The semi-colon is a valid end of script for Microsoft SQL Server 2005 and above, but it might not be acceptable for Oracle.

-SQLBill




The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[djj55]

Is text_value in both tables? If so you would need the alias, example L.Text_value

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[bonjourno]

This is in Oracle. Yes you still need the semi colon to run SQL scripts.

The TEXT_VALUE is only in TABLE1

 

[SQLBill]

Since this is for Oracle, you should post it in one of the Oracle forums then and not in this Microsoft SQL Server forum.

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875