3

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')
Jake Anderson
  • 309
  • 1
  • 4
  • 18
  • By using `case` like here [update-multiple-rows-with-different-values-in-one-query-in-mysql](https://stackoverflow.com/questions/25674737/update-multiple-rows-with-different-values-in-one-query-in-mysql) but I'm not sure it's efficient and you can only do it with raw queries so you will lose your pretty query builder – Pompedup Nov 18 '22 at 01:08

1 Answers1

4

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.

Steve Chavez
  • 931
  • 10
  • 13