How to merge several updates into different tables?

Asked

Viewed 93 times

3

I have 3 updates, and the tables that I have to give the updates have over 2 million records, so it would be impossible to do one by one, since each takes more than half an hour to run, and waiting for one to finish to run the other is not productive.

How to join them in a single update, or even a while/for (I tried to do this, but my knowledge of SQL is limited).

Follows the updates:

update reference_bankaccount set internalid = v.internal_id 
from  contract_reference v 
where v.external_id = externalid
and internalid = ''

update reference_vendor set internalid = v.internal_id 
from  contract_reference v 
where v.external_id = externalid
and internalid = ''

update reference_customer set internalid = v.internal_id 
from  contract_reference v 
where v.external_id = externalid
and internalid = ''

I looked here in Sopt, but the guys have only asked about the change of several columns in the same table, not in different tables.

sql postgresql

  • Could assemble a script, or assemble a spreadsheet and perform the update with a COPY. If you accept, I’ll draw up the answer

  • 3

    You cannot update more than one table in the same statement because the syntax does not allow it. It is not by Atomicity, since if several updates are within the same translation, the atomicity is guaranteed. As commented, just run the 3 instructions at once.

  • 1

    Reginaldo’s reply had solved my problem, but he removed the message. Still, thanks for the personal answers. I didn’t know that putting ; at the end of the transaction he already jumped to the next. Living and learning!

  • Sorry, Rsrs, I withdrew because at the same time I realized I had asked a silly question. Anyway, nice that you solved it so easily.

1 answer

0


I was able to solve it with Reginaldo Rigo’s help as follows:

update reference_bankaccount set internalid = v.internal_id 
from  contract_reference v 
where v.external_id = externalid
and internalid = '';

update reference_vendor set internalid = v.internal_id 
from  contract_reference v 
where v.external_id = externalid
and internalid = '';

update reference_customer set internalid = v.internal_id 
from  contract_reference v 
where v.external_id = externalid
and internalid = '';

Adding the ; at the end of the updates, they run in sequence, without waiting for me to finish one to run the other. :)

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.