SQL: Isnull Function

Question

I'm trying to update an table (se code below). Where, when a match I want to extract the UserID, if there's no match I want to it to be given the value 6000. However, the script doesn't do what I would expect it to do. If U.UserID doesn't exist it doesn't write any value (6000), not if it's null. How can I go around the problem?

/* Ensure Person (UserID)*/
UPDATE #List
SET #List.UserID = ISNULL(U.UserID, '6000')
FROM #List
INNER JOIN table1 t1 ON #List.ID = t1.ID
INNER JOIN User U ON U.FirstName + ' ' + U.Surname = t1.PersonFullname

#List is not joined to any tables in FROM part, that is why you are getting bad results. What is #iList and how it could be joined? — gofr1
– gofr1, Commented Sep 12, 2016 at 7:19

Jaydip Jadhav · Accepted Answer · 2016-09-12 07:35:13Z

4

According to me problem with joining the table with INNER JOIN i.e this will bring only matching records, Now if you want to updated all the records from the First table not only matching as well as other records where UserID is null then solution to this problem is Use of LEFT JOIN

UPDATE List
SET List.UserID = ISNULL(U.UserID, '6000')
FROM #iList List
LEFT JOIN table1 t1 ON List.ID = t1.ID
LEFT JOIN User U ON U.FirstName + ' ' + U.Surname = t1.PersonFullname

edited Sep 12, 2016 at 7:35

answered Sep 12, 2016 at 7:27

Jaydip Jadhav

12.3k6 gold badges26 silver badges41 bronze badges

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

Collectives™ on Stack Overflow

SQL: Isnull Function

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related