Is this safe use of SELECT in an INSERT?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Is this safe use of SELECT in an INSERT?

Graham Holden
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?
Thanks.Graham Holden
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this safe use of SELECT in an INSERT?

Clemens Ladisch
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
PRIMARY KEY.

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));


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this safe use of SELECT in an INSERT?

Graham Holden
In reply to this post by Graham Holden
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
PRIMARY KEY.

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));


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users