Op 20/11/2020 om 01:04 schreef 'Walter R. Ojeda Valiente'
[email protected] [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
<[email protected]
<mailto:[email protected]>> 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
<[email protected]
<mailto:[email protected]>> 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.
--
Plus je dors, mieux je me porte (Gaston Lagaffe)
Hoe meer ik slaap, hoe beter ik me voel (Guus Flater)
The more I sleep, the better I feel (Gomer Goof)
