CODEWITHSUNDEEP
sqlsql-server
Simon Parker
Simon Parker

Reputation: 1834

Insert into using a default value if select returns null

I have a table with a non-null field I wish to populate from another table. Trouble is the query into the other table may return null. How do I get a value (0 will do) when the query returns null? My query is:

update Packages
    set PackageTypeId = (SELECT TOP 1 PackageTypeId
                         FROM PackageTypes
                         WHERE Packages.PackageTypeName = PackageTypes.Name
                         ORDER BY PackageTypeId ASC)

I tried using coalesce, but it still fails:

update Packages
     set PackageTypeId = (SELECT TOP 1 coalesce(PackageTypeId, 0) as id
                          FROM PackageTypes
                          WHERE Packages.PackageTypeName = PackageTypes.Name
                          ORDER BY PackageTypeId ASC)

Any ideas?

Upvotes: 2

Views: 368

Answers (2)

Maciej Nowicki
Maciej Nowicki

Reputation: 347

I recommend UPDATE FROM statement combined with ISNULL function:

UPDATE
    Packages
SET
    Packages.PackageTypeId = ISNULL(PackageTypes.PackageTypeId,0)
FROM
    Packages
INNER JOIN
    (
        SELECT
            PackageTypeId,
            Name
        FROM
            (
                SELECT
                    PackageTypeId,
                    Name,
                    ROW_NUMBER() OVER (PARTITION BY Name ORDER BY PackageTypeId ASC) R
                FROM
                    PackageTypes
            ) X
        WHERE
            R = 1
    ) PackageTypes
ON
    Packages.PackageTypeName = PackageTypes.Name

Note: Subquery return smallest PackageTypeId for each Name in PackageType

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300549

update Packages 
set PackageTypeId = coalesce((SELECT TOP 1 PackageTypeId FROM PackageTypes 
                              WHERE Packages.PackageTypeName = PackageTypes.Name 
                              ORDER BY PackageTypeId ASC), 0)

Upvotes: 6

Related Questions