How to correctly do SQL UPDATE with weighted subselect?
- by luminarious
I am probably trying to accomplish too much in a single query, but have I an sqlite database with badly formatted recipes. This returns a sorted list of recipes with relevance added:
SELECT *, sum(relevance) FROM (
SELECT *,1 AS relevance FROM recipes WHERE ingredients LIKE '%milk%' UNION ALL
SELECT *,1 AS relevance FROM recipes WHERE ingredients LIKE '%flour%' UNION ALL
SELECT *,1 AS relevance FROM recipes WHERE ingredients LIKE '%sugar%'
) results GROUP BY recipeID ORDER BY sum(relevance) DESC;
But I'm now stuck with a special case where I need to write the relevance value to a field on the same row as the recipe. I figured something along these lines:
UPDATE recipes SET relevance=(SELECT sum(relevance) ...)
But I have not been able to get this working yet. I will keep trying, but meanwhile please let me know how you would approach this?
