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.
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)
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);
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.