如何根据优先级更新状态？

Question

我有状态组合列表：

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

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

Answer 1

尝尝这个!

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