Solved

Case with exists with innerjoin in update query

Posted on 2016-07-20
3
36 Views
Last Modified: 2016-07-20
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
Comment
Question by:johnnyg123
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 41721826
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41721828
and of course make sure you have a index on customerorder.customerid to get the update done fast
0
 

Author Comment

by:johnnyg123
ID: 41721838
Great! Thanks!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Database Integrity 1 48
AD and SQL Server 2016 2 25
MS SQL query to show nearest date 6 32
query linked sql table field from access 4 19
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question