CODEWITHSUNDEEP
sql-servert-sql
Androme
Androme

Reputation: 2449

TSQL update check if null

I have a sql that keep give me an exception, and i can not figure out why, my quess is that the avg(x.Points) return null for some reason, how can i make it return 0 in that case?

    UPDATE a
SET a.Points = (SELECT avg(x.Points) FROM 
   (SELECT TOP 5 * 
    FROM [dbo].[AlbumImages] i 
    WHERE i.AlbumId = a.Id 
    AND i.Points > 0 
    ORDER BY i.Points Desc) x)
FROM  [dbo].[Albums] a
    join [dbo].[Users] u on a.UserId = u.Id
WHERE u.DoRanking = 1

This gives me the exception

Cannot insert the value NULL into column 'Points', 
table 'Cosplay.dbo.Albums'; column does not allow nulls. UPDATE fails.

Upvotes: 1

Views: 348

Answers (1)

rene
rene

Reputation: 42414

You can check with ISNULL on the AVG function. ISNULL takes a second parameter to return when the expression is null.

UPDATE a
SET a.Points = (SELECT ISNULL(avg(x.Points),0) 
                FROM (SELECT TOP 5 * 
                      FROM [dbo].[AlbumImages] i 
                      WHERE i.AlbumId = a.Id 
                      AND i.Points > 0 
                      ORDER BY i.Points Desc) x)
FROM  [dbo].[Albums] a
    join [dbo].[Users] u on a.UserId = u.Id
WHERE u.DoRanking = 1

Upvotes: 3

Related Questions