How to insert for all users in PLSQL when there is no data?

Asked

Viewed 39 times

0

Good afternoon!

I’m trying to insert to all users when there is no type entered for all users:

INSERT INTO USUARIO ( TIPO, PRIORITARIO)
SELECT  '130', 'N'
FROM dual
WHERE not exists (SELECT *
       FROM USUARIO
       WHERE tipo = '130' );

but I’m not getting , some idea of how to do ?

sql database oracle pl-sql

  • Claudio, use the [Edit] button to make changes to the question and not the answers, or add a comment

2 answers

1

Do so:

merge into USUARIO a
     using (select '130' tipo, 'N' prioritario from dual) b
        on (a.tipo = b.tipo)
    when not matched then
     insert ( tipo, prioritario) values( b.tipo, b.prioritario )

It could be so too

merge
   into  usuario
   using (select count(*) as count from usuario where tipo = '130') a
     on   (a.count > 0)
 when not matched then
     insert (tipo, prioritario)
     values ('130', 'N');

So it doesn’t have that value, column formatting, but you need to enter the column values twice, which makes it more confusing.

  • Good afternoon, Mr. Reginaldo! I’m not very familiar with PLSQL , first time I see a select like this = * select '130' type, 'N' prioritized * . --> Column value, Interesting. I’ll test tomorrow. Thank you very much for your time,

0

If I understand correctly, you will only create type 130 if it does not exist on the base. So, one IF solves the problem:

IF NOT EXISTS (SELECT * FROM USUARIO WHERE tipo = '130')
BEGIN
    INSERT INTO USUARIO (TIPO, PRIORITARIO) VALUES ('130', 'N')
END

Browser other questions tagged sql database oracle pl-sql

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