query with many OR conditions

Question

query with many OR conditions

ameuse 60211

Newbie

Points: 6
More actions
August 18, 2010 at 2:17 pm

#226378

Newb here. I am trying to make a query with 50 or so "OR" conditions and know there must be a better way to accomplish my goal.
What it looks like now is:
UPDATE email_table
SET email_opt_out='Y'
WHERE email_addr='asdf@snarf.com' OR 'qwerty@reddit.com' (etc etc etc etc)
So I am looking for a way to either reference this list of email addresses in another form or to add them to the query without writing out a huge WHERE statement.
Any suggestions?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ray K SSC-Dedicated Points: 31163 More actions · Answer 1

ameuse 60211 (8/18/2010)
Newb here. I am trying to make a query with 50 or so "OR" conditions and know there must be a better way to accomplish my goal.
What it looks like now is:
UPDATE email_table
SET email_opt_out='Y'
WHERE email_addr='asdf@snarf.com' OR 'qwerty@reddit.com' (etc etc etc etc)
So I am looking for a way to either reference this list of email addresses in another form or to add them to the query without writing out a huge WHERE statement.
Any suggestions?

Try:

WHERE email_addr IN ('asdf@snarf.com', 'qwerty@reddit.com', . . . ) (etc.)

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

WayneS SSC Guru Points: 95460 More actions · Answer 2

I would suggest adding the email addresses to a temp table, then do the update by joining on that.

i.e.

CREATE TABLE #tempEmail (EmailAddress varchar(100))

INSERT INTO #tempEmail

SELECT 'asdf@snarf.com' UNION ALL

SELECT 'qwerty@reddit.com'

UPDATE et

SET email_opt_out='Y'

FROM email_table et

JOIN #tempEmail te

ON te.email_addr = et.EmailAddress

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ameuse 60211 Newbie Points: 6 More actions · Answer 3

Thanks for the tips, I haven't ever used IN.

You wouldnt happen to know of a way I could do it similar to this with all the emails in a txt file...

for N in email_addr.txt do

UPDATE email.table

SET email_optout='Y'

WHERE email_addr=$N

Source-NH SSCarpal Tunnel Points: 4165 More actions · Answer 4

Create a temp table and do a bulk insert from the txt file. Then do as the above suggested, joining the tables together.

Create table #emailaddys (address varchar(100))

bulk insert #emailaddys from '<path to file>'

Leonard Rutkowski SSCrazy Points: 2668 More actions · Answer 5

Unless the email addresses were constantly changing, I would store them in a permanent table, then do the join as suggested. This would allow you to change the table as needed, and not have to bulk insert each time. If the email addresses are constantly changing (ftp once a week, etc.), then bulk insert to temp, and then a join is the way to go.