CODEWITHSUNDEEP
sqlsql-server-2008updates
Fever
Fever

Reputation: 130

Update null and specific value using first value of each column and specific value respectively

I have a requirement to update 30 columns as test1 null = 33 and 33=55, test2 null = 122 and 122 = 55 so on.....If possible then it would better to update with first value of row 1 by id so that I don't need to search and define each value.

SampleData:

id  test1   test2
1   33      122
2   1       122
3   NULL    35
4   3       NULL
5   59      170
6   33      122

ExpectedResult:

id  test1   test2
1   55      55
2   1       55
3   33      35
4   3       122
5   59      170
6   55      55

I am trying as following and it also not updating correctly

UPDATE #tmpOne 
SET test1 = CASE WHEN test1 IS NULL THEN 33 ELSE 55 END,
    test2 = CASE WHEN test2 IS NULL THEN 122 ELSE 55 END

Everyone is welcome for the answer and thanks in advance.

Upvotes: 0

Views: 44

Answers (3)

Shushil Bohara
Shushil Bohara

Reputation: 5656

This is one good alternative:

UPDATE #tmpOne 
SET 
test1 = CASE ISNULL(test1, '') WHEN  '' THEN 33 WHEN 33 THEN 55 ELSE test1 END,
test2 = CASE ISNULL(test2, '') WHEN  '' THEN 122 WHEN 122 THEN 55 ELSE test2 END

Upvotes: 1

Paweł Dyl
Paweł Dyl

Reputation: 9143

If you need it at once, you can use CASE:

UPDATE #tab
SET test1 = CASE WHEN test1=33 THEN 55 WHEN test1 IS NULL THEN 33 ELSE test1 END,
    test2 = CASE WHEN test2=122 THEN 55 WHEN test2 IS NULL THEN 122 ELSE test2 END

Upvotes: 1

Daneel
Daneel

Reputation: 1183

Try something like :

UPDATE table
SET test1 = 55
WHERE test1 = (SELECT test1 FROM table WHERE id = 1)

UPDATE table
SET test1 = (SELECT test1 FROM table WHERE id = 1)
WHERE test1 IS NULL

Upvotes: 0

Related Questions