How to correctly do SQL UPDATE with weighted subselect?

Question

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?

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.

Answer 1

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