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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Combine 2 Update statements

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_Holds
SET Original_or_Resubmittal_Type = 'RESUBMITTAL'
FROM #Billing_Holds bh
INNER JOIN Resubmittal br (nolock)
on bh.Last_Attempted_Billing_ID =br.Billing_ID
where exists (Select br.Billing_ID from Resubmittal br (nolock)
where bh.Last_Attempted_Billing_ID =br.Billing_ID)

UPDATE #Billing_Holds
SET Original_or_Resubmittal_Type = 'ORIGINAL'
FROM #Billing_Holds bh
INNER JOIN Resubmittal br (nolock)
on bh.Last_Attempted_Billing_ID =br.Billing_ID
where 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
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-21 : 19:10:56
Shot in the dark:
UPDATE 
bh
SET
Original_or_Resubmittal_Type =
CASE
WHEN br.Billing_ID IS NOT NULL THEN 'RESUBMITTAL'
ELSE 'ORIGINAL'
END
FROM
#Billing_Holds AS bh
LEFT 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
Go to Top of Page
   

- Advertisement -