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

Question

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.

Shushil Bohara · Accepted Answer · 2016-09-21 08:27:27Z

1

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

answered Sep 21, 2016 at 8:27

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paweł Dyl · Accepted Answer · 2016-09-21 07:43:22Z

1

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

answered Sep 21, 2016 at 7:43

Paweł Dyl

9,1831 gold badge13 silver badges28 bronze badges

Comments

Daneel · Accepted Answer · 2016-09-21 07:40:30Z

0

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

answered Sep 21, 2016 at 7:40

Daneel

1,1934 gold badges16 silver badges37 bronze badges

1 Comment

Fever Over a year ago

sorry its correct but in this way i have to do same for second column which is very lengthy, I wanted the solution in single query. Thanks

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related