CODEWITHSUNDEEP
sql-serversql-server-2012tableau-api
Ryan Gomes
Ryan Gomes

Reputation: 75

SQL or Tableau Replace values with existing one with reference to another field

My problem with the data is 

Id  Name
--------
1   a
2   b
1   a
1   a
1   NULL
2   b
2   b
2   NULL
2   b
2   NULL

ID is unique to the name

I would like the NULL values to be replaced with existing Records.
Or if there are any other alternatives

I have about 2000 IDs so it needs to be automated.

Upvotes: 1

Views: 146

Answers (2)

Ranjana Ghimire
Ranjana Ghimire

Reputation: 1815

try this: replace with your tablename

declare @id int, @name varchar(10)
declare c cursor for
select distinct id,name from  <table> where id is not null and name is not null
open c
fetch next from c into @id,@name
while @@FETCH_STATUS=0
begin
update <table> set name=@name where id=@id
fetch next from c into @id,@name
end
close c
deallocate c

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13237

The below query can use to replace the NULLs with its Id's Name, since Id is unique to the Name.

UPDATE T1 SET T1.Name = T2.ValidName
FROM TestTable T1 
JOIN (  SELECT Id, MAX(Name) AS ValidName
        FROM TestTable
        WHERE Name IS NOT NULL
        GROUP BY Id ) T2 ON T2.Id = T1.Id
WHERE T1.Name IS NULL

Sample execution in SQL-Server:

DECLARE @TestTable TABLE (Id INT, Name VARCHAR (20));

INSERT INTO @TestTable (Id, Name) VALUES
(1, 'a'),
(2, 'b'),
(1, 'a'),
(1, 'a'),
(1, NULL),
(2, 'b'),
(2, 'b'),
(2, NULL),
(2, 'b'),
(2, NULL);

UPDATE T1 SET T1.Name = T2.ValidName
FROM @TestTable T1 
JOIN (  SELECT Id, MAX(Name) AS ValidName
        FROM @TestTable
        WHERE Name IS NOT NULL
        GROUP BY Id ) T2 ON T2.Id = T1.Id
WHERE T1.Name IS NULL

SELECT * FROM @TestTable

Upvotes: 1

Related Questions