CODEWITHSUNDEEP
sql
user2853141
user2853141

Reputation: 23

sql update table using the case

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

If I run this query, I got the following error:

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.

Upvotes: 2

Views: 54

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

You need a join because viLastRecord is a table and it is not defined in the query.

Judging by the syntax, it looks like you are using SQL Server. So, you can try this:

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;

Upvotes: 2

Related Questions