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?