Hi Tomek!

Firebird even behaves differently depending on whether an index is used for the select part or not. Let's say that you have 6 records in your table. Then,

insert into tab1 (...) select (...) from tab1 where PK_tab1 < 100

inserts 6 rows, whereas

insert into tab1 (...) select (...) from tab1 where PK_tab1+0 < 100

inserts 99 rows (assuming PK_tab1 is automatically generated and starting with 1). This is because the first query can use an index whereas the second cannot. It is incorrect and not sensible at all, but still, it is the kind of strange behavior that you sometimes get bitten by and sometimes can take advantage of (until it eventually gets fixed).

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of tomjanczkadao
Sent: 15. september 2009 10:07
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: insert into tab1 (...) select (...) from tab1

There is primary key. In fact, it isn't strict copy of records. Primary key is generated from gen_id(), and there are some data changes on the fly (in select clause).
But I still wonder why select doesn't take a snapshot :)

Regards, Tomek

--- In firebird-support@yahoogroups.com, Sándor Tamás (HostWare Kft.) <sandortamas@...> wrote:
>
> For tables like this, you definitely should have a primary key, like an auto
> increment field. (By the way, when do you need duplicated datas to be in a
> table without unique identifier? )
>
> With that, you can read the last value, and do a select where PRIMARY_ID <=
> MAX_VALUE.
> (in fact, it will be faster than the null compare for temp_field)
>
> SanTa