SQL MATCH LIKE, make the id equal to each other

Question

so i have this code

SELECT NAME, ingredient_id
FROM ingredient_aliases
WHERE (NAME) LIKE '%WATER%' 

it shows me all the similar names to WATER

From image above, all three are Water.

I have a lot of WATER names with NULL ingredient_id. Is there a way i can make their ingredient_id equal to each other?

if you need more information, just tell me. Thank you !

Answer 1

Use a subquery to get the ingredient ID from the non-null rows. Then use that in an UPDATE assignment for all the null rows.

UPDATE ingredient_aliases
SET a.ingredient_id = 
     (SELECT MAX(ingredient_id) AS max_id
      FROM ingredient_aliases
      WHERE name LIKE '%WATER%'
        AND ingredient_id IS NOT NULL)
WHERE name LIKE '%WATER%' 
    AND ingredient_id IS NULL