Is it possible to perform update on 1 table using another condition?

0

I have the following query:

update ivr_contatos,ivr_campanha set ivr_contatos.tentativas = 0 where ivr_contatos.status = 0 and ivr_contatos.tentativas >= ivr_campanha.qtdtentativas

The question is, if I can update in the field tries ivr_contatos table using as premise the field status (ivr_contatos) and qtdtentativas (ivr_campanha)

Structure of tables:

The data can not pass because I do not have it yet.

    
asked by anonymous 18.05.2018 / 14:20

3 answers

4

You need to do this:

UPDATE ivr_contatos
   SET tentativas = camp.quantidadetentativas
FROM ivr_campanha as camp
WHERE ivr_contatos.campanha = camp.id AND
    ivr_contatos.status = 0 AND 
    ivr_contatos.tentativas >= camp.qtdtentativas

Source: Postgresql Documentation (page 1574)

    
19.05.2018 / 14:37
1

Try this:

UPDATE ivr_campanha campanha SET CONTENT='VALOR ALTERADO' WHERE campanha.status = 0 AND campanha.id IN
(SELECT contatos.id FROM ivr_contatos contatos WHERE contatos.campanha = campanha.id and campanha.qtdtentativas > contatos.qtdtentativas);  

link

    
18.05.2018 / 15:11
0

You can also use joins in updades and deletes . In your case, it would look like this:

update ivr_contatos cont
inner join ivr_campanha camp
    on cont.campanha = camp.id
set cont.tentativas = 0
where cont.status = 0 and cont.tentativas >= camp.qtdtentativas

Note that for this example, I am considering that the column ivr_contacts.campaign is the foreign key of ivr_campanha.id , ie it is by these columns that these tables are related.

    
18.05.2018 / 14:47