使用WHERE和LIKE更新Server

Question

我有这样的代码，它似乎不适用于替换。我是不是漏掉了什么

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)),'.',''))

Answer 1

你有个更大的问题。您需要update在FROM子句中定义的别名，而不是表。

您的问题可能是replace()需要一个字符串连接：

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)),'.',''))

Answer 2

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)),'.',''))

不确定是否替换，但如上面所示修改rest代码