The bulk update proc need to generate a single query

[thisago]

Hi, I need to make a lot of updates but the proc:
proc update*[T: Model](dbConn; objs: var openArray[T]) at src/norm/sqlite.nim(357)
updates one by one and it's very slow.

There's a way to create a bulk update statement in sqlite: SQLITE bulk UPDATE statement

I think that I will try to implement it in SQLite module and if works I will create a PR

[moigagoo]

Hi! If there's a matching construct in Postgres, I'd like to encourage you to include its support in your PR as well.

[PhilippMDoerner]

The SQL for bulk-updating in postgres appears to be looking like this:

create table tmp
(
  id serial not null primary key,
  name text,
  age integer
);

insert into tmp (name,age) 
values ('keith', 43),('leslie', 40),('bexley', 19),('casey', 6);

update tmp set age = data_table.age
from
(select unnest(array['keith', 'leslie', 'bexley', 'casey']) as name, 
        unnest(array[44, 50, 10, 12]) as age) as data_table
where tmp.name = data_table.name;

https://stackoverflow.com/questions/7019831/bulk-batch-update-upsert-in-postgresql

[thisago]

Hi! If there's a matching construct in Postgres, I'd like to encourage you to include its support in your PR as well.

Hi, sorry about delay, ok, i can add postgreesql too, I never worked with it but seems to be like SQL syntax. Maybe tomorrow I'il implement

[thisago]

I added this implementation for SQLite, I'll create the PR when the tests succeeds. Now I'll try to add to Postgres