How can i update a table based on a count of another table while using LIKE statement

I know how to update one table's field from another table's count using t开发者_JAVA技巧1.id=t2.id etc.. but i have somewhat typical issue. I have to use LIKE STATEMENT in WHERE clause.

This is something similar i wanted to do.

UPDATE `CATEGORIES`
SET    `num_listings` = (SELECT COUNT(*)
                         FROM   `LISTINGS`
                         WHERE  `LISTINGS`.`CATEGORY` LIKE
                                ws_concat('', "%-", `CATEGORIES`.`ID`, "-%"));  

(Example: I have CATEGORY stored as -25- in the LISTINGS table as a field name CATEGORY)

I understand that i cannot use ws_contact here but is there another way to achieve it?

Thanks in advance.

Unless there is a good reason for the category ID to be represented only by a part of a string in the listings table, the best way to handle such a structure of data is to add a category_id column to the LISTINGS table, and make sure that when adding or editing a listing this column is populated properly.

This would allow to simply JOIN the two tables ON categories.id = listings.category_id and makes much more sense. This would also give better performance by far.

If you do want to keep the DB structure as is, you can use a temporary table, with LIKE and CONCAT:

DROP TABLE IF EXISTS temp;

CREATE TABLE temp AS 
       SELECT categories.id, COUNT(*) AS c
       FROM categories
       JOIN listings ON listings.category LIKE CONCAT('%',categories.id,'%')
       GROUP BY categories.id;

UPDATE categories, temp
SET categories.num_listings = temp.c
WHERE categories.id = temp.id;