CODEWITHSUNDEEP
sql-server
Vijay
Vijay

Reputation: 69

update multiple rows which is having null values

I have table with two columns which id and name. Id contains null values and name contains some name value. There are duplicate rows in the table. The requirement is to update null values (id) with some value which is matching with name value. Find below table

Id      Name
---------------
1   abc
1   abc
1   abc
1   abc
NULL    abc
NULL    abc
NULL    abc
2   xyz
2   xyz
2   xyz
NULL    xyz
NULL    xyz

The above table which is having null values in the id column. I have to update Null values with Id. For example for abc I have to update 1 and for xyz have to update 2.

Upvotes: 1

Views: 1745

Answers (3)

Paulie
Paulie

Reputation: 1

Let's assume the name of the table is 'names'. Try executing the query below.

update names n set n.Id = (select max(Id) from names where upper(Name) = upper(n.Name)) where n.Id is null;

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37337

Give it a go:

update MY_TABLE set id = new_id from
(
  select id [new_id], name [new_name] from MY_TABLE
  where id is not null
) A
where name = new_name

Upvotes: 0

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Use Self Join

update A
    SET Id = B.id
    FROM YourTable A
        INNER JOIN YourTable B
            ON a.Name = B.Name
        WHERE A.id IS NULL
            AND b.Id IS NOT NULL

Upvotes: 2

Related Questions