I want to keep a semi-persistent list of server/port pairs with an associated "index" that can be used to refer to entries elsewhere. Given:
create table Servers ( serverName text, serverPort integer, serverIdx integer unique, primary key ( serverName, serverPort ) )
Is the following "safe" to ensure that the next index value is used for inserted rows? (It seems to work fine from the shell).
insert or ignore into Servers values ( 'MyServer', 12345, ( select count (*) from Servers ) )
I.e. is the "count (*)" guaranteed to be the count before the insert?
sqlite-users mailing list
[hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Thanks for that. For now, any deletions would be "en masse" so gaps wouldn't be a problem, but the max() idea might be useful later.
Sent from my Samsung Galaxy S7 - powered by Three
-------- Original message --------From: Clemens Ladisch <[hidden email]> Date: 08/03/2017 08:59 (GMT+00:00) To: [hidden email] Subject: Re: [sqlite] Is this safe use of SELECT in an INSERT?
Graham Holden wrote:
> insert or ignore into Servers values ( 'MyServer', 12345, ( select count (*) from Servers ) )
> is the "count (*)" guaranteed to be the count before the insert?
Yes. But if any row was ever deleted, that value might alread by used.
You could make serverIdx autoincrementing by making it the INTEGER
If you do not want to change the primary key, you can get a value that
is guaranteed to be unused with max():
INSERT OR IGNORE INTO Servers
VALUES ('MyServer', 12345,
(SELECT ifnull(max(serverIdx), 0) + 1 FROM Servers));