I have a list of 800k id’s that I need

Question

I have a list of 800k id’s that I need

to update a single column for each of them? what’s the best approach doing this?
create 800k update statments and run them?
update table set active=1 where id in (select * from 800k_table)?

#backend #devops #pgsql #programming #russian

0

26.06.2022

10 ответов

29 просмотров

Alexey Bulgakov

central hardware
batch update

10k per run for example

0

26.06.2022

Ilya Anfimov

COPY them to a temporary table and join it in UPDATE.

0

26.06.2022

🙈🙉🙊 Автор вопроса

Ilya Anfimov
COPY them to a temporary table and join it in UPDA...

Will it be faster than use Update.... where id in (select ...)? How would the update look with such join?

0

26.06.2022

Slava Rozhnev SQLtest.online https://phpize.online

🙈🙉🙊
Will it be faster than use Update.... where id in ...

Below you can find an example for update one table from another https://sqlize.online/sql/psql13/efa316b9de74485c1ce98979fa3ae04c/

0

26.06.2022

Ilya Anfimov

🙈🙉🙊
Will it be faster than use Update.... where id in ...

>Will it be faster than use Update.... where id in (select ...)? I don't expect much difference. Both will be reasonably fast, anyway. >How would the update look with such join? This will depend on update and table structures.

0

26.06.2022

🙈🙉🙊 Автор вопроса

Ilya Anfimov
>Will it be faster than use Update.... where id in...

original table is id+active , table list with 800k ids is only 1 column id.

0

26.06.2022

Ilya Anfimov

🙈🙉🙊
original table is id+active , table list with 800k...

UPDATE original SET active=now() FROM ids WHERE original.id=ids.id

0

26.06.2022