当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - do X in column A if Y found in column B

问题描述:

i have done some research on this matter and found that this works and is fairly easy:

UPDATE `table` SET `A`=A+200 WHERE B='Y';

this works obviously if column B has only a single Y value.

but here comes the 'hard' part. column B has multiple values like this: ,Y1,Y2,Y3,Y4,

i would like A+200 to be executed only if Y3 is found in column B regardless of what the other values are. i am not too familiar with arrays or whatever is required to search through column B for the Y3 value. any help is appreciated. thank you!

网友答案:

You can either use FIND_IN_SET or match against a regular expression with REGEXP

Example:

UPDATE `table`
SET `A` = `A` + 200
WHERE FIND_IN_SET('Y3', `B`)

or

UPDATE `table`
SET `A` = `A` + 200
WHERE `B` REGEXP '(^|,)Y3(,|$)'

aaand what glglgl said. Normalize your database: Wikipedia: Database normalization

网友答案:
UPDATE `table` SET `A`=A+200 WHERE B='Y3';
网友答案:
UPDATE `table` SET `A` = A + 200 WHERE `B` LIKE 'Y%'; 

try this. It means update the table where B starts with Y and doen't matter what is next ;)

网友答案:
 UPDATE table` SET A=A+200 where find_in_set('Y3',YOUR_COLUMN);
分享给朋友:
您可能感兴趣的文章:
随机阅读: