Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Combine 2 Update statements
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Petronas
Posting Yak Master

134 Posts

Posted - 09/21/2012 :  18:29:59  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 09/21/2012 :  19:04:02  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/21/2012 :  19:10:56  Show Profile  Reply with Quote
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

Edited by - Lamprey on 09/21/2012 19:12:05
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000