sql更新表使用案例

UPDATE [Current]   
SET    
    C_OS = CASE WHEN [Current].[OS] <> [viLastRecord].[OS] THEN '1' END,   
    C_VER = CASE WHEN [Current].[VER] <> [viLastRecord].[VER] THEN '1' END   
WHERE 
    [Current].VHOST = viLastRecord.VHOST AND   
    [Current].HOSTNAME = viLastRecord.HOSTNAME 

如果我运行此查询，则会收到以下错误:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "viLastRecord.VHOST" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "viLastRecord.HOSTNAME" could not be bound.

请您参考如下方法:

您需要一个join，因为viLastRecord 是一个表，并且未在查询中定义。

从语法来看，您似乎正在使用 SQL Server。所以，你可以试试这个:

UPDATE c  
    SET C_OS = (CASE WHEN c.[OS] <> vlr.[OS] THEN '1' END),   
        C_VER = (CASE WHEN c.[VER] <> vlr.[VER] THEN '1' END)   
    FROM [Current] c JOIN 
         viLastRecord vlr 
         ON c.VHOST = vlr.VHOST AND   
            c.HOSTNAME = vlr.HOSTNAME;