Insert into using a default value if select returns null

Question

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?

Mitch Wheat · Accepted Answer · 2015-04-15 07:02:03Z

6

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

answered Apr 15, 2015 at 7:02

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

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

1 Comment

Simon Parker Over a year ago

That makes bucket loads of sense. Thanks.

Maciej Nowicki · Accepted Answer · 2015-04-15 10:56:22Z

2

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

edited Apr 15, 2015 at 10:56

answered Apr 15, 2015 at 7:07

Maciej Nowicki

3471 silver badge13 bronze badges

4 Comments

A ツ Over a year ago

but the order by and top 1 part is lost

Maciej Nowicki Over a year ago

order by and top 1 is not necessary in this case - mathing rows is done by join. BTW. I'm not sure, but this form should be faster than finding top 1 row for each ID.

A ツ Over a year ago

the order by and top 1 suggest that there are multiple PackageTypeId per PackageTypeName. the original query chooses the one with the least ID, yours not.

Maciej Nowicki Over a year ago

You are right! I've modified query above according to this.

Collectives™ on Stack Overflow

Insert into using a default value if select returns null

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related