Re: [firebird-support] Re: UPDATE OR INSERT in Firebird 3

[Daniel Miller dmil...@amfes.com [firebird-support]]

I experimented with IDENTITY fields myself - based on my experience and 
some older comments here I quit using them. I think they are presently 
suitable for "quick" tables - something that will be append only like a log.

If you're looking for more advanced features, like supporting UPDATE OR 
INSERT (which I use myself), then you're much better off explicitly 
writing the appropriate insert and update triggers. Possibly version 4 
will implement IDENTITY better but I don't use it all now.

Daniel

On 11/20/2020 5:59 AM, 'Walter R. Ojeda Valiente' 
sistemas2000profesio...@gmail.com [firebird-support] wrote:


Hello Herman

I had used UPDATE OR INSERT for several years in several hundred of 
tables. No problem...with Firebird 2.5.x

But with Firebird 3.0.x I can not make it work.

If I write the identity column, doesn't work. If I don't write the 
identity column, doesn't work..

Thank you very much for your answer.

Greetings.

Walter.



On Fri, Nov 20, 2020 at 6:16 AM Herman Viaene herman.via...@edpnet.be 
<mailto:herman.via...@edpnet.be> [firebird-support] 
<firebird-support@yahoogroups.com 
<mailto:firebird-support@yahoogroups.com>> wrote:

    Op 20/11/2020 om 01:04 schreef 'Walter R. Ojeda Valiente'
    sistemas2000profesio...@gmail.com
    <mailto:sistemas2000profesio...@gmail.com> [firebird-support]:

    I use firebird3 from libreoffice base, not directly, but in that
    way I can make (and have done) such insert stattements.
    CREATE TABLE MYTABLE (
      COLUMN1 INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1)
    NOT NULL,
      COLUMN2 VARCHAR(20));


    ALTER TABLE MYTABLE ADD CONSTRAINT PK_MYTABLE PRIMARY KEY (COLUMN1);

    I have similar tables


    Then, is I write it:
    UPDATE OR INSERT INTO MYTABLE (COLUMN1, COLUMN2) VALUES (NULL,
    'TEST')

    The message is:
    Validation error for column "MYTABLE"."COLUMN1", value "*** null
    ***".

    That is quite normal and correct, since you define column1 as not
    null, and now you try to insert a null value in it.


    And if I write it:
    UPDATE OR INSERT INTO MYTABLE (COLUMN2) VALUES ('TEST')

    I have written similar insert statements and they work OK, BUT: I
    write either an insert statement OR an update statement. The two
    are fundamentally different as to the primary key handling:
    in an insert statement you do not give a value to the PK, since it
    is generated by the system.
    in an update statement you have to define on which rows (all or by
    giving a value for the PK or by a select statement) the update has
    to apply.

    I must confess I've never tried to use "UPDATE OR INSERT", and I
    wonder about it.  I googled and checked the syntax, and I think
    your statement will try to match your value 'TEST' against the PK,
    and that does not work of course. You would have to use the
    "matching (column2)" in your statement.

    Just my 2c

    Herman Viaene




    The message is:
    UPDATE OR INSERT field list does not match primary key of table
    MYTABLE.


    There is some solution? Or it is impossible to use an IDENTITY
    column with an UPDATE OR INSERT?

    Greetings.

    Walter.




    On Thu, Nov 19, 2020 at 8:55 PM Walter R. Ojeda Valiente
    <sistemas2000profesio...@gmail.com
    <mailto:sistemas2000profesio...@gmail.com>> wrote:

        I forget to say that my IDENTITY column is the Primary Key of
        MyTable, therefore if I don't write it an error happens
        because...the table needs a Primary Key.

        On Thu, Nov 19, 2020 at 8:52 PM Walter R. Ojeda Valiente
        <sistemas2000profesio...@gmail.com
        <mailto:sistemas2000profesio...@gmail.com>> wrote:

            Hello everybody

            A long, long time without writing neither reading this
            group, mostly because all that I need about Firebird I knew.

            But now, I have a doubt.

            With Firebird 2.5.x I can have an auto-incremental column
            and use it in an UPDATE OR INSERT, but such thing is not
            possible with Firebird 3.

            With Firebird 2.5.x a generator and a trigger are created
            and the value of the column is put automatically. So, if
            I write:
            UPDATE OR INSERT INTO MyTable (MyColumn1, MyColumn2)
            VALUES (NULL, 12345);

            and MyColumn1 is auto-incremental its value is set for
            the Firebird engine.

            but...if I use the new IDENTITY type in Firebird 3 I
            always have an error, writing MyColumn1 in the UPDATE OR
            INSERT or not writing.

            So, my question is:

            Is it possible to use UPDATE OR INSERT with an IDENTITY
            column?

            Thanks in advance.

            Greetings.

            Walter.