How to correctly do SQL UPDATE with weighted subselect?
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%'
) resu开发者_C百科lts 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?
Edit: Evaluating Peter's solution, I ran into some serious performance issues, due to the amount of data the operations had to be performed on. I wrote a little Rebol script that does the loops and and the finding and does commits in 1000-row batches. It completed in a couple of minutes.
You really should get your model fixed (see below).
If you can't, the following query should work (I tried it and it worked as I expected).
Update recipes
Set relevance =
(
Select Case When ingredients Like '%milk%' Then 1 Else 0 End
+ Case When ingredients Like '%flour%' Then 1 Else 0 End
+ ...
From recipes r2
Where r2.recipe_id = recipes.recipe_id
)
It uses Case
to sum up all matched ingredients.
Suggested model:
Table
recipes
Table
ingredients
Table
recipe_ingredients
recipe_id
ingredient_id
精彩评论