sql - 如何根据 Precedence 更新状态？

问题描述

我有状态组合器：

Agents
Association
conf.delegate
Embassies
exhibitors
Ing-advert
Inquiry
Low Priority
Misc
PrioAdvert
Priority
Removes
Sec-Advert
Secondary
Service Provider
VIP
VIP Exhibitor
VIP Visitor
Visitors

我想要一个这样的查询：

Misc状态更改（更新）为Secondary/visitor/priority/Agents/Assciation/Inquiry/Exhibitors/Removes/Embassies/VIP/VIP exbitors/VIP visitors

但Secondary/priority/visitor/Agents/Assciation/Inquiry/Exhibitors/Removes/Embassies/VIP/VIP exbitors/VIP visitor不应更改为Misc.

同样的方式Secondary状态更改为visitor/priority/Agents/Assciation/Inquiry/Exhibitors/Removes/Embassies/VIP/VIP exbitors/VIP visitors

但visitor/priority/Agents/Assciation/Inquiry/Exhibitors/Removes/Embassies/VIP/VIP exbitors/VIP visitors不应更改为Secondary.

我在存储过程中编写了以下查询，它将状态更新为我想要的任何内容。

set 
  cmp.status= 
    case when 
      cmp.status='' OR 
      cmp.status IS NULL OR 
      cmp.status IS NOT NULL 
    then  
      dupl.status 
    else 
      cmp.status 
    end 
from companyinfo cmp
inner join #DuplTempTable dupl on cmp.ID=dupl.ID

我不知道如何根据优先级编写更新查询。我知道我必须用 CASE WHEN它来实现它。

标签： sqlsql-server

update cmp set cmp.status= case when cmp.status='' OR cmp.status IS NULL OR cmp.status IS NOT NULL then dupl.status else cmp.status end from companyinfo cmp inner join #DuplTempTable dupl on cmp.ID=dupl.ID

sql - 如何根据 Precedence 更新状态？

问题描述

解决方案

推荐阅读