update multiple records in one database request

Niraj Chourasia

Hi  all,

I have a table A in which i want to update multiple records based on the unique id. When i am using edit record statement it's only updating single row. please let me know how to achieve it.

Thanks

Ray Deck

So you have a table that contains a field called F, and N records have a value of X in field F, and you want to update those N records, but not the rest of the records? 

I would query all records with a filter that F needs to contain X, and then loop over those records. Within the loop, run an "edit record" setting the field to the value you are looking to change, based on the actual primary key field of the record you pulled in that loop. 

So the logic isn't super-complicated. Let me know if you have qs or want to do a short zoom call to set up your situation. 

Niraj Chourasia

  so is it not possible without loop? like we do on mysql query or other db query

Ray Deck

 To my understanding, Xano does not have an "UPDATE TABLE SET G='Y' WHERE H='X'" equivalent. Nor is direct SQL connectivity available without their enterprise plan. So to update multiple records involves multiple round trips in the technique above.  might set me straight on that. 

Niraj Chourasia

 oh really, so would it be efficient for large number of records if we use loop and edit?

Ray Deck

 This is one of those "question of fact" situations that I would recommend checking with a stopwatch - see how long it takes in your particular situation. 

My intuition: since the database is co-located with your code on the same, dedicated virtual server (that is my understanding of the setup in a paid xano account) the main efficiency loss of the above approach is not having the opportunity to wrap the updates in a single transaction. Put a different way, looping to generate multiple updates is not perfectly efficient performance-wise, but I'm not sure that it would be that much slower, and it could be more robust to error. Again, the Xano peeps may correct me, since I am not on the team nor privy to all the underlying tech 

Valentin R

I was able to achieve this with a direct database query. Just need to figure out an underlying table

Pawel Magdanski

@Valentin R So you were able to bulk edit records with direct query?

Valentin R

@Pawel Magdanski yes

The steps for me were:
1. Determine table name https://docs.xano.com/working-with-data/functions/database-requests/direct-database-query#finding-your-database-identifier

2. Do this query to get the underlying table name

SELECT viewname, definition
FROM pg_views
WHERE viewname = 'x1_1';

3. Run this to update records that match conditions

UPDATE mvpw1_1 SET xdo = jsonb_set(xdo, '{column_name}', '"new_value"', true)
WHERE id BETWEEN 1 AND 3;

Xavier Agapé

If you want to Update your datas querying something else than ID this is an exemple :

UPDATE mvpw1_1 SET xdo = jsonb_set(xdo, '{mapping_id}', '"1285"', true)

WHERE
(xdo->>'companies_id' = '32')
AND (xdo->>'JournalCode' IN ('BANK_1', 'BANK_2'));

Hope it will help :)

Valentin R

@Xavier Agapé I am having trouble with updating by ID. I have a large array of IDs and if I just do this, I get an error
WHERE id IN (?);

"?" is an array from the function stack in my case

do I need to transform an array somehow first?

Xavier Agapé

Hi @Valentin R , sorry for the delay ,
This is how I solved the array pain point :

AND (xdo->>'JournalCode' = ANY(?::text[]));

Valentin R

@Xavier Agapé, thanks for helping out
Still fighting with it, trying this:

UPDATE mvpw1_1
SET xdo = jsonb_set(xdo, '{value_1}', '"test"', true)
WHERE xdo->>'id' = ANY(?::text[]);

getting this:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: malformed array literal: "[1,2,3]" DETAIL: Missing "]" after array dimensions. (SQL: UPDATE mvpw1_1 SET xdo = jsonb_set(xdo, '{value_1}', '"test"', true) WHERE xdo->>'id' = ANY([1,2,3]::text[]);)

Any clues?

Valentin R

@Xavier Agapé still wondering how to make it work. Let me know if you succeed, please.

Cameron B

You should be able to fix it by altering the array as it's received inside the statement.

This is my example query:

UPDATE mvpw661_2522
SET xdo = jsonb_set(xdo, '{phone}', '"test-phone-number"')
WHERE id = ANY(string_to_array(trim(both '[]' from ?), ',')::int[])

The sorcery is in the WHERE statement - the right half lets us use the string_to_array function. Since the array is being seen as a string, we can technically treat it like one and split it into its own parts. When we recombine them, we cast them to be integers so that we can then use them in our statement. Hope this helps!

Valentin R

Bravo, @Cameron B , it worked!

Next challenge: how to update two columns with the same query, for the same ids. I need to set "status_timestamp" column to "now" value. Was able to achieve it separately, but a few attempts to combine failed

UPDATE mvpw1_1

SET
xdo = jsonb_set(xdo, '{status_timestamp}', ?, true)

WHERE id = ANY(string_to_array(trim(both '[]' from ?), ',')::int[]);

Valentin R

Ok, got it to work with a sub-query

UPDATE mvpw1_1
SET
xdo = jsonb_set(
jsonb_set(xdo, '{status}', '"PROCESSING"', true),
'{status_timestamp}', ?, true
)
WHERE id = ANY(string_to_array(trim(both '[]' from ?), ',')::int[]);


For anyone curious - this update for 50 entries takes 0.05-0.06 secs while a loop takes 0.25-0.26. 5 times faster.

Santiago

Hello, I am trying to do the same but I only need all the records to take the value of false in the used field, I have the following query UPDATE mvpw1_44 SET xdo = jsonb_set(xdo, '{used}', false) WHERE (xdo->>'used' = true) Could you help me to know if I have something wrong?, It already worked for me