I have below example table (ID is the primary):
ORGTable:
id | Type | Info ----+---------+------- 1 type1 info1 1 type2 info2 1 type3 info3 2 type4 info4 2 type5 info5 3 type6 info6 3 type7 info7 4 type8 info8 5 type9 info9
and below query would return data for ID 1 (ID is the primary), ex.
select * from ORGTable where ID ='1'
Results of the query
id | Type | Info ----+---------+------- 1 type1 info1 1 type2 info2 1 type3 info3
my issue is i would like to update only one row from the above results, for example row 1 to be like below:
Results after update
id | Type | Info ----+---------+------- 1 type99 info99 1 type2 info2 1 type3 info3
Advertisement
Answer
i have bulk of data, would like to update them in one query where Type=’type1′ and Info=’info1′ then update
This will group all data according to your condition, then update them
;WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rn FROM ORGTable WHERE type='type1' AND Info='info1' ) UPDATE cte SET type = 'type99',info = 'info99' WHERE rn = 1
If you just want to update one value:
update TOP (1) ORGTable set type = 'type99', info = 'info99' where id = '1' and type = 'type1' and Info = 'info1'