基于多个case语句和连接更新基表 [英] Update base table based on several case statements and joins

问题描述

 

我有一个包含声明的基表，我需要更新该表中的StatusId。此状态ID需要根据"已接受"和"已拒绝"状态进行更新，并检查此声明ID是否位于多个表中。 

基表有2个字段：  claimID，SubmissionID 

此基表需要根据以下两个条件进行更新： 

条件1：检查基表中的claimID是否存在在查询表中名为ResponseClaim_Lookup（ResponseClaimID = claimID） 

   如果存在 然后检查查找表field_number字段line_number ='000' 然后将基表中的StatusID更新为'A001' 

   如果存在，请检查是否 查找表字段field_number ='111' 然后将基表中的StatusID更新为'B002'

条件2：如果ResponseClaim_Lookup中不存在ClaimID，则检查RejectedClaim_Lookup表中的ClaimID（rejectedclaimID = ClaimId） 

   如果存在 然后检查字段line_number ='222' 然后将Base表中的StatusID更新为'Rej_001' 

   如果存在，则检查field_number ='333' 然后将基表中的StatusID更新为'Rej_003'

任何示例方案都会有所帮助。 

谢谢， 

Vishal。 

解决方案

Hi Vishal，

 

你想要这个吗？如果我有任何不正确的理解，
请与您分享您的表结构和一些示例数据以及您的预期结果。这样我们就可以得到正确的方向并进行一些测试。

 if if（从[Base table]中选择a.claimID a加入ResponseClaim_Lookup b on a.claimID = b.ResponseClaimID）
 begin 
 update a 
 set a.StatusID ='A001'
 from [Base table] a joinClaim_Lookup b on a .claimID = b.ResponseClaimID其中b.line_number ='000'
 
更新a 
设置a.StatusID ='B002'
从[基表]加入ResponseClaim_Lookup b on a.claimID = b.ResponseClaimID其中b.field_number ='111'
 end 
 
 if exists（从[Base table]中选择a.claimID a.claimID = b上的joinClaim_Lookup b .rejectedclaimID）
 begin 
更新a 
设置a.StatusID ='Rej_001'
来自[Base table] a.claimID = b.rejectedclaimID b的连接ResponseClaim_Lookup b。 line_number ='222'
 
更新a 
设置a.StatusID ='Rej_003'
来自[Base table] a.claimID = b.re上的joinClaim_Lookup b jectedclaimID其中b.field_number ='333'
 end 

希望它可以帮到你。

 

最好的问候，

拉结

Hi , 

I have a base table containing claims and I need to update StatusId's in that table. This status ID's need to be updated based on 'Accepted' and 'Rejected' status and checking if this claimID resides in several tables. 

Base table has 2 fields :  claimID,SubmissionID 

This base table needs to be updated based on 2 conditions : 

Condition 1: Check if claimID in base table exists in lookup table called ResponseClaim_Lookup ( ResponseClaimID = claimID) 

   if exist  then check if lookup table field_number field line_number = '000'  then update StatusID in Base table to 'A001' 

   If exists then check if  Lookup table field field_number = '111'  Then update StatusID in base table to 'B002'

Condition 2: if ClaimID does not exist in ResponseClaim_Lookup , then check for ClaimID in RejectedClaim_Lookup table ( rejectedclaimID = ClaimId) 

   if exist  then check if field line_number = '222'  then update StatusID in Base table to 'Rej_001' 

   If exists then check if field_number = '333'  Then update StatusID in base table to 'Rej_003'

Any example scenarios would help. 

Thank you , 

Vishal. 

解决方案

Hi Vishal,

 

Would you like this one? If I have any incorrect understanding , please share us your table structure and some sample data along with your expected result. So that we’ll get a right direction and make some test.

if exists (select a.claimID from [Base table] a join ResponseClaim_Lookup b on a.claimID = b.ResponseClaimID)
begin 
update a
set a.StatusID ='A001'
from [Base table] a join ResponseClaim_Lookup b on a.claimID = b.ResponseClaimID  where b.line_number='000'

update a
set a.StatusID ='B002'
from [Base table] a join ResponseClaim_Lookup b on a.claimID = b.ResponseClaimID  where b.field_number ='111'
end 

if exists (select a.claimID from [Base table] a join ResponseClaim_Lookup b on a.claimID = b.rejectedclaimID)
begin 
update a
set a.StatusID ='Rej_001'
from [Base table] a join ResponseClaim_Lookup b on a.claimID = b.rejectedclaimID  where b.line_number='222'

update a
set a.StatusID ='Rej_003'
from [Base table] a join ResponseClaim_Lookup b on a.claimID = b.rejectedclaimID  where b.field_number ='333'
end 

Hope it can help you.

 

Best Regards,

Rachel

这篇关于基于多个case语句和连接更新基表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！