2shady4u/godot-sqlite

Is it possible to do bulk update_rows

ibutab opened this issue · 3 comments

ibutab commented

My question is if it's possible to do bulk update_rows, in the documentation it's shown as one row but it's named row(s). With insert_rows it's possible to insert multiple rows. I can always loop the updates, but would prefer to do them in bulk since it's more efficient and cuts down on loading time.

Here is snippet from documentation/code:
Change name of 'Amanda' to 'Olga' and her age to 30
db.update_rows(table_name, "name = 'Amanda'", {"AGE":30, "NAME":"Olga"})

2shady4u commented

Hello @ibutab

There should not be any reason why the command couldn't be used to update all rows that fit the condition.
For example (if using the db from the demo-project):

db.update_rows(table_name, "age > 25", {"SALARY":0})

Will set all the salaries of all the persons with an age above 25 to 0.
In this case that would be: "Paul", "Robert" and "Julia"

ibutab commented

Hello @ibutab

There should not be any reason why the command couldn't be used to update all rows that fit the condition.

For example (if using the db from the demo-project):


db.update_rows(table_name, "age > 25", {"SALARY":0})

Will set all the salaries of all the persons with an age above 25 to 0.

In this case that would be: "Paul", "Robert" and "Julia"

@2shady4u
How could I do this with the case of say I want to update ID: 1 to amount: 100 and ID: 2 to amount 85. Where it's row specific and not a "general rule" like every x over y in your example.

2shady4u commented

Well in that case you'll have to use the query()-method (or query_with_params()-method if you have unsanitized user input), as such:

db.query("BEGIN TRANSACTION;")
db.query("UPDATE " + table_name + " SET amount = 100 WHERE id = 1;")
db.query("UPDATE " + table_name + " SET amount = 85 WHERE id = 2;")
db.query("END TRANSACTION;")
  • 👍1