Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Petronas
Posting Yak Master
134 Posts |
Posted - 2012-09-21 : 18:29:59
|
Hi ,I have two update statements based on the exist and not exist clause. How can I combine the both in my update statement.UPDATE #Billing_HoldsSET Original_or_Resubmittal_Type = 'RESUBMITTAL'FROM #Billing_Holds bhINNER JOIN Resubmittal br (nolock)on bh.Last_Attempted_Billing_ID =br.Billing_IDwhere exists (Select br.Billing_ID from Resubmittal br (nolock) where bh.Last_Attempted_Billing_ID =br.Billing_ID) UPDATE #Billing_HoldsSET Original_or_Resubmittal_Type = 'ORIGINAL'FROM #Billing_Holds bhINNER JOIN Resubmittal br (nolock)on bh.Last_Attempted_Billing_ID =br.Billing_IDwhere not exists (Select br.Billing_ID from Resubmittal br (nolock) where bh.Last_Attempted_Billing_ID =br.Billing_ID) Thank you,Petronas |
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-09-21 : 19:04:02
|
Why are you using NOLOCK when you are updateing data?(I know that doesn't answer your question, but first things first)-Chad |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-09-21 : 19:10:56
|
Shot in the dark:UPDATE bhSET Original_or_Resubmittal_Type = CASE WHEN br.Billing_ID IS NOT NULL THEN 'RESUBMITTAL' ELSE 'ORIGINAL' ENDFROM #Billing_Holds AS bhLEFT OUTER JOIN Resubmittal AS br on bh.Last_Attempted_Billing_ID = br.Billing_ID If that doesn't work post your DDL, DML and Expected output:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
|
|