Update multiple rows in a single query in a Supabase Database (postgres)

Question

I looking to update multiple rows via a single query in Supabase. I'm struggling to find an example of how to do it online.

For this example here is how the data exists the database's 'food' table:

id	title	qty
1	Apple	10
2	Banana	8
3	Mango	4

What i would like to do is update the 3 qty fields in the single query (this code doesn't work but should give you an idea of what i am after).

const { data: item_data, error: item_error } = await supabase
   .from('food')
   .update({ qty: 11, }).eq('id', '1')
   .update({ qty: 9, }).eq('id', '2')
   .update({ qty: 6, }).eq('id', '3')

Answer 1

You can do it with an upsert():

const { data, error } = await supabase
  .from('food')
  .upsert([{id: 1, qty: 11}, {id: 2, qty: 9}, {id: 3, qty: 6}])

Additionally you can also do it with a SQL function as mentioned here.