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

Question

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

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

Good afternoon!

I'm trying to insert all users when there is not a 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 can not insert, any idea how to do?

sql oracle pl-sql

asked by anonymous 30.05.2018 / 19:00

2 answers

Connection class mysqli in php and SELECT [duplicate] Save form Razor

score 1 · Answer 1

Do this:

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 that way 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');

That way it does not have that formatting value, column, but you need to report the values of the columns twice, which makes it more confusing.

score 0 · Answer 2

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

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