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

By using case like here 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
– Pompedup, Commented Nov 18, 2022 at 1:08

Steve Chavez · Accepted Answer · 2022-11-18 04:48:29Z

15

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.

answered Nov 18, 2022 at 4:48

Steve Chavez

1,25416 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lam Dương · Accepted Answer · 2024-06-04 04:01:38Z

-1

Actually, the .update function does not work when you do it like that. You can run it synchronously to update those values. Here is my solution; it works for me now!

try {
  const mappings = [
    { qty: 11, id: '1'},
    { qty: 9, id: '2'},
    { qty: 6, id: '3'},
  ]
  for (const mapping of mappings) {
    const { ref, value } = mapping
    
    const { data, error } = await supabase
      .from('food')
      .update({ qty: value }) 
      .eq('id', ref)  value

    if (error) throw error
    console.log(`Updated ${ref} to ${value}`)
  }
} catch (error) {
  console.error('Error during update process:', error)
}

edited Jun 4, 2024 at 4:01

answered Jun 4, 2024 at 4:00

Lam Dương

11 bronze badge

1 Comment

Luca Sep 11 at 12:24

this is incredibly inefficient as you are firing an individual request to supabase for every entry in the mappings array

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related