Count word in text column in MySQL

Question

This question is about MySQL Query. I have three tables:

brand (consists of: text)
englishkeyword (consists of ID, word)
englishkeywordmodel (consists of: ID, score)

I want to count how many words in 'text' with the word which is already determined in 'word'. Then the result is stored in 'score'. This is my code:

UPDATE englishkeywordmodel
SET score=(SELECT COUNT(*)FROM brand WHERE text LIKE (SELECT word FROM englishkeyword WHERE ID=2 LIKE ('%', word, '%')))
WHERE ID=2;

I got this error:

1241 - Operand should contain 1 column(s)

I say many thanks for answer!

Abhishek Sharma · Accepted Answer · 2015-11-10T06:19:30.000

2

Use LIMIT because Your subquery is selecting two or more columns

SELECT word FROM englishkeyword WHERE ID=2 AND Column LIKE
CONCAT('%', word ,'%')  limit 1

edited Nov 10 '15 at 06:19

answered Nov 10 '15 at 06:12

Abhishek Sharma

6,689
1
14
20

wait, sorry, I make mistake with my question. I have made editing. btw, I still got error with that query – razin kac Nov 10 '15 at 06:17
WHERE ID=2 LIKE ('%', word, '%') this is not valid – Abhishek Sharma Nov 10 '15 at 06:18
WHERE ID=2 **AND Column** LIKE ('%', word, '%') change this – Abhishek Sharma Nov 10 '15 at 06:18
SELECT word FROM englishkeyword WHERE ID=2 AND Column LIKE CONCAT('%', word ,'%') limit 1 try this – Abhishek Sharma Nov 10 '15 at 06:24
it's my pleasure :)- @caknizar – Abhishek Sharma Nov 10 '15 at 06:28
hmm I have additional question related to this posting: is that possible if I do for some ID in one query? suppose I have 10 ID so how to query in a time? – razin kac Nov 10 '15 at 06:50
Use **where id IN (1,2,3,4,5...)** @caknizar – Abhishek Sharma Nov 10 '15 at 07:02
it works but from my observation the counted word is only first ID, so the ID value after is same with first ID. eg. ID 1 is counted 4, then 2,3,4,5 = 4 (whereas isn't) – razin kac Nov 10 '15 at 08:38
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/94716/discussion-between-cak-nizar-and-rahautos). – razin kac Nov 10 '15 at 14:06

Count word in text column in MySQL

1241 - Operand should contain 1 column(s)

1 Answers1