CODEWITHSUNDEEP
mysql
sanchitkhanna26
sanchitkhanna26

Reputation: 2243

IN() not working - MySQL

I have this simple query -:

SET @rejects = "'song1','song2'"; 
SELECT * FROM media WHERE share_src IS NULL || share_src NOT IN(@rejects)

But this doesn't work, and also shows the rows with share_src = song1 and share_src = song2.

What is the trouble in my query. Thanks for help..

Upvotes: 0

Views: 52

Answers (4)

georgecj11
georgecj11

Reputation: 1636

Try with find_in_set function

SET @rejects = "song1,song2"; 
SELECT * FROM media WHERE share_src IS NULL ||  NOT FIND_IN_SET(share_src, @rejects)

Upvotes: 0

John Woo
John Woo

Reputation: 263853

Here's a version for dynamic sql

SET @rejects = "'song1','song2'"; 
SET @sql = NULL;

SET @sql = CONCAT('SELECT * 
                    FROM media 
                    WHERE share_src IS NULL OR share_src NOT IN(',@rejects,')');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Upvotes: 1

juergen d
juergen d

Reputation: 204894

IN does not work with one string. You have either use really comma separated values and not just one string or use a Prepared Statement.

That works:

SELECT * FROM media 
WHERE share_src IS NULL || share_src NOT IN('song1', 'song2')

but that doesn't

SELECT * FROM media 
WHERE share_src IS NULL || share_src NOT IN("'song1','song2'")

Upvotes: 1

Randy
Randy

Reputation: 16673

you are treating this as all one string.

you would have to execute the entire statement as a string instead... (in Oracle EXECUTE IMMEDIATE)

Upvotes: 0

Related Questions