[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 55
  • Last Modified:

Case with exists with innerjoin in update query

Here is some sample data from 2 tables I have

Table 1  
MailCustomer

CustomerID
12345
56768


Table 2
CustomerOrder

CustomerID     MailFlag
12345
66666

I am trying to write an update query that will :

update the Customer flag to 'T' on Customer Order table If the Customer Number exists in the Mail Customer table.  

update the Customer flag to 'F' on Customer Order table If the Customer Number does not exist in the Mail Customer table.  

Given data above table below should look like below after query

CustomerOrder

CustomerID     MailFlag
12345                T
66666                F


Was thinking of using case with inner join but not sure how
0
johnnyg123
Asked:
johnnyg123
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is the syntax principles i would suggest

update co
 set mailflag = case when exists( Select * from customer c where c.customerid = co.customerid ) the 'T' else 'F' end
from customerorder co

or

update co
 set mailflag = case when c.customerid is not null then 'T' else 'F' end
from customerorder co
left join customer c on  c.customerid = co.customerid
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and of course make sure you have a index on customerorder.customerid to get the update done fast
0
 
johnnyg123Author Commented:
Great! Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now