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

Asked

Viewed 113 times

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 doubt is, if I can update the field attempts of the table ivr_contacts using as a premise the field status (ivr_contacts) and qtdtentativas (ivr_campaign)

Table structure:

inserir a descrição da imagem aqui inserir a descrição da imagem aqui

The data I can’t get through because I don’t have it yet.

database postgresql query

  • puts the structure of the tables, and an example of the data please

  • @Rovannlinhalis added structure...

  • 1

    I don’t know if the postgree will accept, but normally I do it this way update ivr_contatos
set ivr_contatos.tentativas = 0 
where ivr_contatos.status = 0 
 and ivr_contatos.tentativas >= (select qtdtentativas from ivr_campanha where id=ivr_contatos.campanha)

  • @Rodrigok. B Thus, it of the relation error. : column "ivr_contacts" of relation "ivr_contacts" does not exist

  • 2

    Changing Rodrigo’s code a little bit: update ivr_contatos set tentativas = 0 where status = 0 and tentativas >= (select x.qtdtentativas from ivr_campanha x where x.id=ivr_contatos.campanha)

  • 1

    Rovann, just for understanding, x. represents an Alias for the right ivr_campaign table?

  • 1

    To answer your question to Rovann, yes. And to complement, regarding the mistake you wrote, just follow the guidance of the colleague, which will work.

  • It worked as I wanted. I could post as an answer to mark it please?

Show 3 more comments

3 answers

4

You need to do it like 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: Documentation of Postgresql (page 1574)

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

http://sqlfiddle.com/#! 9/64b8c2/1

0

It is also possible to use joins in upties and déletes. 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_campaign.id, that is, it is by these columns that these tables relate.

  • Thank you Leandro, I will test this solution.

  • 2

    it gave syntax error in Inner

Browser other questions tagged database postgresql query

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