CODEWITHSUNDEEP
sqlsql-serverreplacesql-like
Iasha
Iasha

Reputation: 77

SQL Server Update using WHERE and LIKE

I have this code that doesnt seem to work with the REPLACE. Am I missing something here

UPDATE EMIR
SET Series = CL.Underlying_Security_Name
FROM EMIR e
INNER JOIN CALYPSO cl
    ON e.Internal_ID = cl.Trade_ID
    AND e.Internal_ID_Type = 'CL'
    WHERE cl.Underlying_Security_Name like 'CMBX.NA.%'REPLACE((SUBSTRING(REPLACE(Underlying_Security_Name,'CMBX.NA.',''),CHARINDEX('.',REPLACE(Underlying_Security_Name,'CMBX.NA.',''))+1,2)),'.',''))

Upvotes: 0

Views: 312

Answers (2)

navdeep singh
navdeep singh

Reputation: 1

UPDATE emir
    SET Series = (select CL.Underlying_Security_Name
FROM EMIR e, 
     CALYPSO cl
     Where
 e.Internal_ID(+)  = cl.Trade_ID AND e.Internal_ID_Type = 'CL'
And cl.Underlying_Security_Name like 'CMBX.NA.%'REPLACE((SUBSTRING(REPLACE(Underlying_Security_Name,'CMBX.NA.',''),CHARINDEX('.',REPLACE(Underlying_Security_Name,'CMBX.NA.',''))+1,2)),'.',''))

Not sure about replace but modify the rest code as above

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You have a bigger problem. You need to update the alias defined in the FROM clause, not the table.

It is possible that your problem with the replace() needing a string concatenation:

UPDATE e
    SET Series = CL.Underlying_Security_Name
FROM EMIR e INNER JOIN
     CALYPSO cl
     ON e.Internal_ID = cl.Trade_ID AND e.Internal_ID_Type = 'CL'
WHERE cl.Underlying_Security_Name like 'CMBX.NA.%' + REPLACE((SUBSTRING(REPLACE(Underlying_Security_Name,'CMBX.NA.',''), CHARINDEX('.', REPLACE(Underlying_Security_Name,'CMBX.NA.',''))+1,2)),'.',''))

Upvotes: 2

Related Questions