Update multiple rows with set of values sql

Question

I want to update multiple rows using update query. There is one primary key. I don't want to update the primary key. suppose

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

is the table. I want to update the name, address and age sequentially. I have a set of new values as input. like

("yJ","ht43",34)

("rt","fd43",36)

("hg","hgd4",40)

("ui","udg6",28)

How to update the rows sequentially with these values with update query?

Answer 1

You can do this by assigning a Row number to both your existing values, and your new values. Then join the two data sets on this row number, and update accordingly:

WITH YourTableRanked AS
(   SELECT  t.*,
            RowNum = ROW_NUMBER() OVER(ORDER BY ID)
    FROM    YourTable AS t 
), NewValues  AS
(   SELECT  t.*,
            RowNum = ROW_NUMBER() OVER(ORDER BY Name)
    FROM    (VALUES
                ('yJ','ht43',34),
                ('rt','fd43',36),
                ('hg','hgd4',40),
                ('ui','udg6',28)
            ) AS t (Name, Address, Age)
)
UPDATE  T
SET     Name = v.Name,
        Address = v.Address,
        Age = v.Age
FROM    YourTableRanked AS t
        INNER JOIN NewValues AS v
            ON v.RowNum = t.RowNum;

It is worth noting that any kind of TOP, or ranking operation is either not valid syntax, or not particularly useful in the absence of an ORDER BY clause. So when you refer to updating rows "sequentially", in order for sequentially to have any meaning you need to define the order of this sequence. In the queries I have posted I have ordered by Name in your new values, and ID for your existing data, but you may wish to change this to suit your 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);

WITH YourTableRanked AS
(   SELECT  t.*,
            RowNum = ROW_NUMBER() OVER(ORDER BY ID)
    FROM    #t AS t 
), NewValues  AS
(   SELECT  t.*,
            RowNum = ROW_NUMBER() OVER(ORDER BY Name)
    FROM    (VALUES
                ('yJ','ht43',34),
                ('rt','fd43',36),
                ('hg','hgd4',40),
                ('ui','udg6',28)
            ) AS t (Name, Address, Age)
)
UPDATE  T
SET     Name = v.Name,
        Address = v.Address,
        Age = v.Age
FROM    YourTableRanked AS t
        INNER JOIN NewValues AS v
            ON v.RowNum = t.RowNum;

SELECT  *
FROM    #T;

DROP TABLE #T;