SQL script, to insert emails into a table, and then change them

Asked

Viewed 51 times

3

I have a script, which does random insertions of emails into a table, after these insertions, I need to find a way to recheck these inserted emails, and change them

This is a part of the Script, I pass him a lot of emails (for example 5), and he will create 5 e-mail ([email protected].... [email protected]), what I’m not getting is after these insertions, replace these emails in an UPDATE. I need to replace these same inserted emails with other emails, such as ([email protected].... [email protected]) 

FOR i IN 1..V_QTD_EMAILS LOOP
INSERT INTO G_CADASTRO.PX_EMAIL
  (
  ID,
  CD_DADOS_XXX_XXX_XXX,
  EMAIL,
  DT_INCLUSAO,
  CD_USUARIO
  )
VALUES
  (
    (SELECT MAX(ID) + 1
    FROM G_CADASTRO.PPX_EMAIL),
    V_CD_DADOS_PX_ESTAB,
    'KAUE' || i || '@EMAIL.COM.BR',
    SYSDATE,

    4993
  );
END LOOP;

COMMIT;

I tried to include this block but it didn’t work:

FOR i IN 5..V_QTD_EMAILS loop
  UPDATE G_CADASTRO.PX_EMAIL
     SET EMAIL = 'MATHEUS_' || i || '@EMAIL.COM.BR'
    WHERE CD_DADOS_PX_ESTAB = V_CD_DADOS_PX_ESTAB
END LOOP;

Could you help me?

sql oracle

  • Make the same loop as Insert, and use your EMAIL on where EMAIL = 'KAUE' || i || '@EMAIL.COM.BR' and CD_USUARIO = 4993 and on your set use SET EMAIL = 'MATHEUS_' || i + 5 || '@EMAIL.COM.BR'

  • but the update will happen when ? you call the script again later, passing 10 for example ? or just call script 1x and it gives the Insert and then gives the update ?

  • Rovann Linhalis, exactly that, the script runs only once, so Insert and update occur in the same script, then the test is finished. Actually the flow occurs like this: Checks the Parent Table >> If there is already e-mail, erases everything, and inserts new data, then this data should undergo an UPDATE, which alters the emails

  • Why don’t you insert the emails correctly in the first loop?

1 answer

1


Person, I solved with the following SCRIPT:

FOR i IN 1..V_QTD_EMAILS loop
  UPDATE G_CADASTRO.PX_EMAIL
      SET EMAIL = 'TSTALTERACAO_' || i || '@EMAIL.COM.BR'
      WHERE EMAIL = 'KAUE' || i || '@EMAIL.COM.BR'
      AND CD_USUARIO = 4993;
END LOOP;

Thanks for all your help

  • mark the answer as accepted ;)

  • 1

    To mark my own answer, I must wait two days :\

Browser other questions tagged sql oracle

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