Query error with WHERE AND COUNT on Oracle. "SQL command not properly ended"

Question

I have the following query:

INSERT INTO TB_CLASSIFICACAO_UG 
    (COD_CLASSIFICACAO_UG, TXT_DESCRICAO, IND_ATIVO) 
VALUES 
    (SEQ_COD_TB_CLASSIFICACAO_UG.nextval, 'TESTANDO', 1)
WHERE 
    (SELECT COUNT(TXT_DESCRICAO) FROM TB_CLASSIFICACAO_UG WHERE TXT_DESCRICAO = 'TESTANDO') = 0

This query generates the error:

"SQL command not properly ended"

But if I turn only the first part it works.

INSERT INTO TB_CLASSIFICACAO_UG 
    (COD_CLASSIFICACAO_UG, TXT_DESCRICAO, IND_ATIVO) 
VALUES 
    (SEQ_COD_TB_CLASSIFICACAO_UG.nextval, 'TESTANDO', 1)

And if I turn the second part too.

(SELECT COUNT(TXT_DESCRICAO) FROM TB_CLASSIFICACAO_UG WHERE TXT_DESCRICAO = 'TESTANDO') = 0

I wonder what I’m doing wrong?

What I want after all is like an INSERT IF NOT EXISTS. Any query suggestions? I’ve tried other ways than this and it didn’t work.

Answer 1

The command INSERT (SQL ANSI) does not accept the clause WHERE, this need to insert only if it does not exist is relatively common but its implementation changes according to the bank, an approach I recommend is to check this in your application code so that the logic becomes clearer.

If you choose to do everything in SQL it is still possible, as follows(in oracle):

begin
  insert into TB_CLASSIFICACAO_UG
         (COD_CLASSIFICACAO_UG, TXT_DESCRICAO, IND_ATIVO) 
  values (SEQ_COD_TB_CLASSIFICACAO_UG.nextval, 'TESTANDO', 1)
  ;
  commit;
exception
  when DUP_VAL_ON_INDEX
  then ROLLBACK;
end;

Answer 2

Try reversing the order of your query to something similar to the following:

INSERT INTO TB_CLASSIFICACAO_UG 
    (SELECT COD_CLASSIFICACAO_UG, TXT_DESCRICAO, IND_ATIVO FROM TB_CLASSIFICACAO_UG WHERE TXT_DESCRICAO <> 'TESTANDO')
VALUES 
(SEQ_COD_TB_CLASSIFICACAO_UG.nextval, 'TESTANDO', 1)

Answer 3

For conditional insertion in Oracle you can use the clause MERGE

MERGE
   INTO  TB_CLASSIFICACAO_UG
   USING (SELECT COUNT(TXT_DESCRICAO) AS count FROM TB_CLASSIFICACAO_UG WHERE TXT_DESCRICAO = 'TESTANDO') s
     ON   (s.count > 0)
 WHEN NOT MATCHED THEN
     INSERT (COD_CLASSIFICACAO_UG, TXT_DESCRICAO, IND_ATIVO)
     VALUES (SEQ_COD_TB_CLASSIFICACAO_UG.nextval, 'TESTANDO', 1);

Testing on the Sqlfiddle: http://sqlfiddle.com/#! 4/3944a4/2

Answer 4

For a record to be entered only if it does not yet exist, the databases have unique key restrictions.

In your example, just turn the TXT_DESCRICAO field into a single key using the command:

ALTER TABLE TB_CLASSIFICACAO_UG
ADD CONSTRAINT TB_CLASSIF_TXT UNIQUE (TXT_DESCRICAO);

This way, any routine or query will be prevented from entering a value for TXT_DESCRICAO that already exists in the table.

Answer 5

I managed to come up with a solution to my problem:

INSERT INTO TB_CLASSIFICACAO_UG 
    (COD_CLASSIFICACAO_UG, TXT_DESCRICAO, IND_ATIVO) 
VALUES 
    (SEQ_COD_TB_CLASSIFICACAO_UG.nextval, (SELECT '" + descricao + "' FROM dual WHERE NOT EXISTS (SELECT NULL FROM TB_CLASSIFICACAO_UG WHERE TXT_DESCRICAO = '" + descricao + "')) , 1);