Using Intersect in a IF statement

Question

Post reply

Using Intersect in a IF statement

Blue Rabbit

SSC Enthusiast

Points: 190
More actions
August 31, 2011 at 1:23 pm

#242636

Assume table1 and table2. col1 in table1 has a row values 'HELLO WORLD' and 'HELLO ALL', which can also be found in col2 of table2.
SELECT col1 FROM table1 INTERSECT SELECT col2 from table2;
Returns:
HELLO WORLD
HELLO ALL
Problem: I want to use this in a IF statement. If the number of rows returned is > 0, then I want to set a flag to 'Y'. I have tried a couple of methods and came up empty. Does anyone have any advice? Thanks
Mike

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

you were a little loose on the detaisl as to what flag you want to update...you might need to provide more detials.

IF EXISTS can help i think:

IF EXISTS(

SELECT col1

FROM table1

INTERSECT

SELECT col2

from table2;)

UPDATE SOMETABLE SET FLAG='Y'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 2

Something like:DECLARE @Flag CHAR(1)

IF EXISTS (SELECT Col1 FROM table1 INTERSECT SELECT col2 from table2)

BEGIN

SET @Flag = 'Y'

END

EDIT: bah too slow. 🙂

Blue Rabbit SSC Enthusiast Points: 190 More actions · Answer 3

Lowell, your answer was spot on. I did want to update an extracted table if true. Your code worked perfectly, Thanks!

Lowell SSC Guru Points: 323518 More actions · Answer 4

Blue Rabbit (8/31/2011)
Lowell, your answer was spot on. I did want to update an extracted table if true. Your code worked perfectly, Thanks!

woot! glad i could help! i like to think that a best guess code example is better than asking for more details.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!