Firebird, Insert com Where not exists

Question

Firebird, Insert com Where not exists

Asked 9 years, 3 months ago

Viewed 5,303 times

4

I am trying to insert a record into the table, but this record will only be inserted if it no longer exists there, so I am trying to do so:

INSERT INTO R01 (NUMERO,EXPORTADOR, IMPORTADOR, DATAANTECIPADO, STATUS)
VALUES
(64, 'CCB TESTE LTDA.', 'CCB TESTE LTDA.', (select     cast('Now' as date) from rdb$database), 'A')
WHERE NOT EXISTS ( SELECT * FROM R01 WHERE NUMERO=64 ,SERIE='CTE' );

The error message is this:

Dynamic SQL Error. SQL error code = -104. Token Unknown - line 4, char 1. WHERE.

2

If there is a more elegant way to catch the date in Firebird I would also appreciate

– SneepS NinjA

2015/07/20 at 14:10
This syntax does not exist. Firebird has a nice balcony of "UPDATE OR INSERT". See: http://www.firebirdsql.org/refdocs/langrefupd25-update-or-insert.html. If you have no update and if the record already exists you will simply do nothing, then a IF should solve your problem: http://www.janus-software.com/fbmanual/manual.php?book=psql&topic=98. If you can solve the problem with these commands post a complete answer to help the next one who passes by here better ;-)

– Caffé

2015/07/20 at 17:51
@Anus with that one update or Insert it almost solved, the problem is that it updates every time the record is already there, going back to the initial state, my STATUS field will one hour change, and if you run sql again it will go back to the initial STATUS

– SneepS NinjA

2015/07/20 at 18:18
1: More elegant way to pick up the date: CURRENT_DATE variable

– Edison de Brito

2016/11/25 at 17:22

3 answers

2

I understand what you need; you can do a process and run when you need to. Just take into account of course, that you will have to make some adjustments to it as I do not know your database. Next:

CREATE OR ALTER PROCEDURE SP_STACKOVERFLOW (
    p_numero integer,
    p_serie varchar(3),
    p_exportador varchar(255),
    p_importador varchar(255))
as
declare variable existe integer;
declare variable dataatual integer;
begin

  select
     count(r01.<id da sua tabela>)
  from
    R01
  where
    numero = :P_NUMERO and
    serie = :p_serie
  into
    :existe;

  if (existe = 0) then
  begin

    dataatual = current_date;

    INSERT INTO R01 (NUMERO, EXPORTADOR, IMPORTADOR, DATAANTECIPADO, STATUS)
    VALUES
        (:p_numero, :p_exportador, :p_importador, :dataatual, 'A');
  end

end

To use Procedure, run a script with the following code:

execute procedure SP_STACKOVERFLOW(64, 'CTE', 'CCB TESTE LTDA.', 'CCB TESTE LTDA.');

I hope I’ve helped!

1

I think that’s it, as soon as possible I’ll test

– SneepS NinjA

2015/07/22 at 13:34
1

Thanks, settled with proc same, mysql is so simple...

– SneepS NinjA

2015/07/22 at 18:46

Browser other questions tagged sql firebird

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

by Alison Dennis • 21 points · Answer 1 · 2018-01-23T18:06:25+00:00

Good afternoon,

This post already has some time, but I needed this feature today and I managed to solve with the example below (Firebird), based on the previous answer:

Insert Into TBCFOP
  (CODCFOP, DESCRICAO)
Select '5.505', 'REM DE MERCADORIAS ADQ OU RECEB P/ FORM DE LOTE P/ EXP'
  From RDB$DATABASE
 Where Not Exists
       (
         Select 1
           From TBCFOP
          Where CODCFOP = '5.505'
       );

by William • 1 point · Answer 2 · 2016-11-25T19:20:44+00:00

Good afternoon,

It would not be the right way but you can 'fetch' from dummy values in this way by using 'Where':

    INSERT INTO R01 (NUMERO,EXPORTADOR, IMPORTADOR, DATAANTECIPADO, STATUS)
    select 64, 'CCB TESTE LTDA.', 'CCB TESTE LTDA.', 
    (select cast('Now' as date) from rdb$database), 'A')
    from dummy
    WHERE NOT EXISTS ( SELECT 1 FROM R01 WHERE NUMERO=64 ,SERIE='CTE' );