How to correctly execute SQL UPDATE with a weighted subquery?

Question

I'm probably trying to do too much in one query, but I have a sqlite database with poorly formatted recipes. This returns a sorted list of recipes with added relevance:

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 now I’m stuck in a special case when I need to write the relevance value in a field on the same line as the recipe. I understood something like that:

UPDATE recipes SET relevance=(SELECT sum(relevance) ...)

But I still have not been able to do this. I will continue to try, but meanwhile, please let me know how you approach this?

Edit: After evaluating Peter's solution, I ran into some serious performance issues due to the amount of data that had to be executed. I wrote a little Rebol script that makes loops as well as detection and commits in 1000 rows. It ended in a couple of minutes.

Answer 1

You really have to set your model (see below).

If you cannot, the following query should work (I tried, 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 Caseto summarize all agreed ingredients.

---

Recommended Model:

• Table recipes

• Table ingredients

• Table recipe_ingredients

  • recipe_id
  • ingredient_id