CODEWITHSUNDEEP
sql-serverisnull
user3052850
user3052850

Reputation: 178

SQL: Isnull Function

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

Upvotes: 2

Views: 259

Answers (1)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

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

Upvotes: 4

Related Questions