sql server - update multiple rows with set of values sql -

sql server - update multiple rows with set of values sql -

i want update multiple rows using update query. there 1 primary key. don't want update primary key. suppose

id name address age 1 re3 20 2 dg ht5 21 3 hf ujy6 23 4 ku tr5 25

is table. want update name, address , age sequentially. have set of new values input. like

("yj","ht43",34)

("rt","fd43",36)

("hg","hgd4",40)

("ui","udg6",28)

how update rows sequentially these values update query

you can assigning row number both existing values, , new values. bring together 2 info sets on row number, , update accordingly:

with yourtableranked ( select t.*, rownum = row_number() over(order id) yourtable t ), newvalues ( select t.*, rownum = row_number() over(order name) (values ('yj','ht43',34), ('rt','fd43',36), ('hg','hgd4',40), ('ui','udg6',28) ) t (name, address, age) ) update t set name = v.name, address = v.address, age = v.age yourtableranked t inner bring together newvalues v on v.rownum = t.rownum;

it worth noting kind of top, or ranking operation either not valid syntax, or not particularly useful in absence of order by clause. when refer updating rows "sequentially", in order sequentially have meaning need define order of sequence. in queries have posted have ordered name in new values, , id existing data, may wish alter suit needs.

full example:

create table #t (id int, name varchar(2), address varchar(5), age int); insert #t (id, name, address, age) values (1, 'as', 're3', 20), (2, 'dg', 'ht5', 21), (3, 'hf', 'ujy6', 23), (4, 'ku', 'tr5', 25); yourtableranked ( select t.*, rownum = row_number() over(order id) #t t ), newvalues ( select t.*, rownum = row_number() over(order name) (values ('yj','ht43',34), ('rt','fd43',36), ('hg','hgd4',40), ('ui','udg6',28) ) t (name, address, age) ) update t set name = v.name, address = v.address, age = v.age yourtableranked t inner bring together newvalues v on v.rownum = t.rownum; select * #t; drop table #t;

sql-server table sql-update union sequential