CODEWITHSUNDEEP
mysqldatabaseset
user1854392
user1854392

Reputation: 91

MYSQL - Updating a SET Value

Say for example you had a CD Database, where you wanted to increase the CD prices of all CD's with the genre 'Pop' by 10%. However the type Genre is a SET. Where it can be in multiple genre's, such as RnB and Rock.

My code is as follows:

UPDATE CD
set price = price * 1.1
WHERE genre = 'Pop';

However my code is only updating rows where the Genre is ONLY pop. If the Genre is 'Rock,Pop,RnB', it is not updated. What am i doing wrong?

Upvotes: 1

Views: 111

Answers (3)

glutorange
glutorange

Reputation: 993

The best way to access SET values in queries is FIND_IN_SET:

UPDATE CD
set price = price * 1.1
where FIND_IN_SET('Pop', `genre`)

More information about FIND_IN_SET can be found here: http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set

Upvotes: 0

Kermit
Kermit

Reputation: 34055

You need to use LIKE (Documentation):

... WHERE genre LIKE '%Pop%'

This will match if your genre is "Rock,Pop,RnB."

You should be aware that you are using a non-normalized structure. A better design would be to have a genre reference table:

CD_Genre (CD, Genre)

Upvotes: 2

Don
Don

Reputation: 555

use like keyword

UPDATE CD set price = price * 1.1 WHERE genre like '%Pop%';

Upvotes: 1

Related Questions