1

I need a help with this query I make an insert that returns me an id, then I need to run an UPDATE with that id returned...

WITH disca AS ( 
    INSERT INTO callcenter.campanha_cliente_chamada 
        (   id_campanha_cliente,  
                cod_contato, 
                telefone,  
                telefone_discar, 
                id_status_chamada,  
                tentativa, 
                monitorando, 
                acionar_proxima,
                datahora_update, 
                datahora_registro 
        ) VALUES (154424,1824704,'(11) 2524-9636','1125249636',1,1,0,0,CURRENT_TIMESTAMP::timestamp(0),CURRENT_TIMESTAMP::timestamp(0)) 
    RETURNING id_campanha_cliente_chamada 
) UPDATE callcenter.campanha_cliente_chamada 
  SET accountcode = ('D-'||id_campanha_cliente_chamada||'-'||tentativa)::varchar(128) 
  WHERE id_campanha_cliente_chamada = disca;
3
  • @dezso = (SELECT id_campanha_cliente_chamada FROM disca); ?? May 22 '17 at 13:09
  • Do you want to update the rows that you have just inserted? May 22 '17 at 13:10
  • @ypercubeᵀᴹ Yes May 22 '17 at 13:11
3

You don't need to INSERT and then UPDATE - and it isn't advisable anyway, to insert/update/delete the same rows twice in one statement. You can calculate the values and then do the INSERT:

WITH disca 
  ( id_campanha_cliente_chamada,
    id_campanha_cliente, cod_contato, 
    telefone, telefone_discar, 
    id_status_chamada, tentativa, monitorando, acionar_proxima,
    datahora_update, datahora_registro 
  ) 
AS 
  ( VALUES 
      ( nextval('callcenter.campanha_cliente_chamada_id_campanha_cliente_chamada_seq'),
        154424, 1824704, 
        '(11) 2524-9636', '1125249636', 
        1, 1, 0, 0, 
        CURRENT_TIMESTAMP::timestamp(0), CURRENT_TIMESTAMP::timestamp(0)
      )
  ) 
INSERT INTO callcenter.campanha_cliente_chamada 
  ( id_campanha_cliente_chamada,
    id_campanha_cliente, cod_contato, 
    telefone, telefone_discar, 
    id_status_chamada, tentativa, monitorando, acionar_proxima,
    datahora_update, datahora_registro,
    accountcode
  ) 
SELECT
    id_campanha_cliente_chamada,
    id_campanha_cliente, cod_contato, 
    telefone, telefone_discar, 
    id_status_chamada, tentativa, monitorando, acionar_proxima,
    datahora_update, datahora_registro,
    ('D-' || id_campanha_cliente_chamada || '-' || tentativa)::varchar(128)
FROM 
    disca;

You can find the actual name of the sequence that has to be put in nextval('...') with \d campanha_cliente_chamada.

6
  • This is accusing me that column id_campanha_cliente_clamada does not exist on line 27 May 22 '17 at 13:31
  • Does the table have a id_campanha_cliente_chamada column? Try the edit. May 22 '17 at 13:34
  • Yes, but it is an auto increment field, and this value I only have it after INSERT May 22 '17 at 13:35
  • It's the SERIAL, I see. May 22 '17 at 13:36
  • Yes, it is a serial field May 22 '17 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.