Search code examples
postgresqlsupabasesupabase-database

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

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')
Solution

  • 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.