Quantcast

Equivalent syntax in sqlite

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Equivalent syntax in sqlite

Joseph L. Casale
Whats the trick with SQLites working set to format a single statement with parameters
where if a row exists for a given criteria, returns its Id, otherwise insert and return the
last_insert_rowid()?

For example:
CREATE TABLE Foo (
    Id     INTEGER PRIMARY KEY NOT NULL,
    ColA TEXT    NOT NULL,
    ColB TEXT    NOT NULL
);

So the statement always returns the Id scalar value for an existing row or the new
insert? Not sure case can do accomplish that, if/begin/end is not an option...

Thanks,
jlc
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Equivalent syntax in sqlite

Simon Slavin-3

On 26 Apr 2017, at 9:42pm, Joseph L. Casale <[hidden email]> wrote:

> Whats the trick with SQLites working set to format a single statement with parameters
> where if a row exists for a given criteria, returns its Id, otherwise insert and return the
> last_insert_rowid()?

It has to be two operations.

INSERT OR IGNORE INTO MyTable …;
SELECT rowid FROM MyTable WHERE …;

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Equivalent syntax in sqlite

David Raymond
With the comment that the insert or ignore method there will only work if there's an explicit unique constraint on your given criteria.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Simon Slavin
Sent: Wednesday, April 26, 2017 4:46 PM
To: SQLite mailing list
Subject: Re: [sqlite] Equivalent syntax in sqlite


On 26 Apr 2017, at 9:42pm, Joseph L. Casale <[hidden email]> wrote:

> Whats the trick with SQLites working set to format a single statement with parameters
> where if a row exists for a given criteria, returns its Id, otherwise insert and return the
> last_insert_rowid()?

It has to be two operations.

INSERT OR IGNORE INTO MyTable …;
SELECT rowid FROM MyTable WHERE …;

Simon.
_______________________________________________
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Equivalent syntax in sqlite

Joseph L. Casale
From: sqlite-users [mailto:[hidden email]] On
Behalf Of David Raymond
Sent: Wednesday, April 26, 2017 3:00 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Equivalent syntax in sqlite
 
> With the comment that the insert or ignore method there will only work if
> there's an explicit unique constraint on your given criteria.

Yup, the table does have one. Thanks for the help guys.
jlc
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Equivalent syntax in sqlite

J Decker
If you change from an auto increment to a GUID/UUID  you can simply use
'REPLACE INTO' and you don't have to worry about the select, because you'll
already know the ID.

http://www.sqlitetutorial.net/sqlite-replace-statement/



On Wed, Apr 26, 2017 at 4:05 PM, Joseph L. Casale <[hidden email]
> wrote:

> From: sqlite-users [mailto:[hidden email]]
> On
> Behalf Of David Raymond
> Sent: Wednesday, April 26, 2017 3:00 PM
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] Equivalent syntax in sqlite
>
> > With the comment that the insert or ignore method there will only work if
> > there's an explicit unique constraint on your given criteria.
>
> Yup, the table does have one. Thanks for the help guys.
> jlc
> _______________________________________________
> 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
Loading...