alexking
asked on
Update multiple lines with CASE statement
I have a table with, say 1000 clients in it. I need to update a field in my table for each client with a different flag based on a mix of field values from a view that is in another database. The value will be different for each client in the table.
I am using the syntax below but I am missing something - any help greatly appreciated.
I am using the syntax below but I am missing something - any help greatly appreciated.
USE [DB2]
GO
DECLARE @TV bit
DECLARE @EV bit
SET @TV = (SELECT [Test1] FROM [DB1].[dbo].[vwClientTable] WHERE [DB1].[dbo].[vwClientTable].[MainID1] = [DB2].[dbo].[ClientTable].[MainID])
SET @EV = (SELECT [Test2] FROM [DB1].[dbo].[vwClientTable] WHERE [DB1].[dbo].[vwClientTable].[MainID1] = [DB2].[dbo].[ClientTable].[MainID])
UPDATE
[DB2].[dbo].[Client]
SET
[FIELD1] =
(
CASE
WHEN @TV = 1 AND @EV = 1 THEN -1003
WHEN @TV = 1 AND @EV = 0 THEN -1001
ELSE -1002
END
)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much - that was really helpful and I get it now :)
ASKER