create table Attributes
(
    id int, 
    AttributeName nvarchar(255), 
    AttributeValue nvarchar(255)
)

insert into Attributes
values
 (1, 'BuiltNo', '001')
,(1, 'ManagerName', 'x')
,(1, 'PlantAddress', 'NY')
,(2, 'BuiltNo', '002')
,(2, 'ManagerName', 'y')
,(2, 'PlantAddress', 'NSW')
,(3, 'BuiltNo', '003')
,(3, 'ManagerName', 'z')
,(3, 'PlantAddress', 'QLD')

I want to update BuiltNo, ManagerName and PlantAddress where id = 1 in a single update query. Apparently we can not have 2 where conditions in the same query hence looking for different solution.

update Attributes 
set AttributeValue = '*001'  where AttributeName = 'BuiltNo' ,
set  AttributeValue = '*UpdatedValue'   where AttributeName = 'ManagerName' 
where id = 1

CodePudding user response：

You can use a join on a values clause to specify the conditions and new values to apply such as the following:

update Attributes
set AttributeValue = NewAttributeValue
from dbo.Attributes
join (values
  (1, 'BuiltNo', '*001'),
  (1, 'ManagerName', '*UpdatedValue')
) modifications (id, AttributeName, NewAttributeValue)
  on Attributes.id=modifications.id and Attributes.AttributeName=modifications.AttributeName;

Which yields the results:

CodePudding user response：

Just a thought, pass a JSON string

Example

Declare @I int = 1
Declare @J varchar(max) = '{"BuiltNo":"*001","ManagerName":"*UpdatedValue" }'


Update A
   set AttributeValue =B.Value
 From  Attributes A
 Join ( Select * from openjson(@J) ) B
 On A.id=@I and A.AttributeName=B.[key] collate SQL_Latin1_General_CP1_CI_AS

Updated Table

CodePudding user response：

use case when for a simple approach.

    UPDATE Attributes 
    SET AttributeValue =  CASE  
                        WHEN AttributeName = 'BuiltNo' THEN '*001'
                        WHEN AttributeName = 'UpdatedValue' THEN 'ManagerName'  
                        ELSE AttributeName 
                    END 
    WHERE id = 1