How to update multiple records without using multiple update queries?

Question

I have 4 tables need to update likewise ( need to update around 50 records in each table hence 50 update queries each):

table1:

id website_id   other_column
1   14            33
2   12            90
3   56            51
....

same for table2, .....

Need to update website_id by other new website_id

for example: 14 need to replace with 67,

12 need to replace with 34 ,

56 need to replace with 92,

likewise....

Note: I have a data regarding which website_id need to replace with which new website_id.

Around 50 multiply by 4 = 200 update queries is not a feasible solution!

What is the feasible way other than executing multiple update queries?

Sorry for incomplete question previously, now updated. Thanks! — VBMali
– VBMali, Commented Oct 10, 2018 at 12:15
If you want to the same update or calculation it's easy to update multiple rows at the same time. It all depends on what you do/don't put in the WHERE clause. If your updates are all different, then there's nothing wrong with multiple statements. You need all the specifics changes no matter what. Single statement doesn't make it any easier. — user10461681
– user10461681, Commented Oct 10, 2018 at 12:25

user330315 · Accepted Answer · 2018-10-10 12:22:23Z

1

You can update all tables in a single data modifying CTE which also means you only need to specify the replacements once:

with replacement (old_id, new_id) as (
  values 
     (12, 34),
     (56, 92), 
     .... other old/new mappings ...
     (14, 67)
), t1_update as (
  update t1 
    set website_id = r.new_id
  from replacement r
  where r.old_id = t1.website_id
), t2_update as (
  update t2
    set website_id = r.new_id
  from replacement r
  where r.old_id = t2.website_id
), t3_update as (
  update t3
    set website_id = r.new_id
  from replacement r
  where r.old_id = t3.website_id
)
update t4
  set website_id = r.new_id
from replacement r
where r.old_id = t4.website_id;

Online example: https://rextester.com/OQI6307

answered Oct 10, 2018 at 12:22

user330315

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

Comments

Hassan Voyeau · Accepted Answer · 2018-10-10 12:41:53Z

0

Could look something like this. Can be made dynamic by looping through the rows/tables in table_mapped_changes.

UPDATE
    table1
SET
    id = c.new_id
FROM
    table_mapped_changes c
ON 
    id = c.old_id AND
    c.table_name = 'table1'

edited Oct 10, 2018 at 12:41

answered Oct 10, 2018 at 12:23

Hassan Voyeau

3,7144 gold badges25 silver badges25 bronze badges

2 Comments

Hassan Voyeau Over a year ago

Can you provide the Postgres documentation that says, do not repeat the target table ....?

user330315 Over a year ago

postgresql.org/docs/current/static/sql-update.html - see the from_list explanation "Note that the target table must not appear in the from_list, unless you intend a self-join"

Collectives™ on Stack Overflow

How to update multiple records without using multiple update queries?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related