我有一个带有非空字段的表,希望从另一个表中填充。问题是查询到另一个表中可能返回null。当查询返回null时,如何获取一个值(将为0)?
我的查询是:

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

我尝试使用合并,但仍然失败:
update Packages
     set PackageTypeId = (SELECT TOP 1 coalesce(PackageTypeId, 0) as id
                          FROM PackageTypes
                          WHERE Packages.PackageTypeName = PackageTypes.Name
                          ORDER BY PackageTypeId ASC)

有任何想法吗?

最佳答案

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

关于sql - 如果select返回null,则使用默认值插入,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/29643550/

10-09 01:48