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
[firebird-support] <firebird-support@yahoogroups.com> wrote:
>
>
> Op 20/11/2020 om 01:04 schreef 'Walter R. Ojeda Valiente'
> 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> 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> 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.
>>>
>>
>
