Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

This kind of question often results in answers along the lines of application logic such as: "If the row exists get the ID else create it and then get the ID". But the SQL way of doing things is subtly different: "insert a new row where it doesn't already exist. Get the ID of the row". Based

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

Jay A. Kreibich wrote: > On Fri, Jan 04, 2013 at 10:55:43AM +0100, Krzysztof scratched on the wall: >> Hi, >> >> When I use INSERT OR IGNORE, if insertion fail (record exists), >> then sqlite3_last_insert_rowid does return nothing. Is exists similar >> solution which: >> 1. If insert success then

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

On 1/4/2013 8:40 AM, Krzysztof wrote: BTW: About storing dictionary tables in memory. What is faster: - Load all "contries" table to memory and locate it locally (I'm using Free Pascal / Lazarus) - Create unique index on country name and select from countries table on each insert to customers

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

On 4 Jan 2013, at 1:04pm, Igor Tandetnik wrote: > Have your program keep a list of already-inserted countries, complete with > their IDs, in memory. Look up against this in-memory structure. There are > only about 200 countries in the world. Or just premake the table with

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

On 4 Jan 2013, at 12:08pm, Krzysztof wrote: > I want split > repeated columns to dictionary tables like: > > table customers > - name > - surname > - id_country // FK to table countries > > I'm looking for one command which before inserting to "customers" will look > to table

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

On 1/4/2013 7:08 AM, Krzysztof wrote: CSV has one table for example: name | surname | country. I want split repeated columns to dictionary tables like: table customers - name - surname - id_country // FK to table countries I'm looking for one command which before inserting to "customers" will

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

On Fri, Jan 04, 2013 at 10:55:43AM +0100, Krzysztof scratched on the wall: > Hi, > > When I use INSERT OR IGNORE, if insertion fail (record exists), > then sqlite3_last_insert_rowid does return nothing. Is exists similar > solution which: > 1. If insert success then return new rowid > 2. If

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

Clemens Ladisch wrote: > Krzysztof wrote: >> When I use INSERT OR IGNORE, if insertion fail (record exists), >> then sqlite3_last_insert_rowid does return nothing. > > If your unique key is the rowid, then you already know the ID that > you tried to insert. > If your unique key is not the rowid,

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

Ok I have done this with two commnads where first check if record exist. I tried do this with one command because I want to speed up importing from csv. CSV has one table for example: name | surname | country. I want split repeated columns to dictionary tables like: table customers - name -

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

Maybe is faster to swap the commands: 1. Select. If found done. 2. Insert (not doing ignore to get an error if it fails) and then get the rowid from sqlite3_last_insert_rowid. Patrik On 01/04/2013 11:18 AM, Simon Slavin wrote: > > On 4 Jan 2013, at 9:55am, Krzysztof wrote: > >>

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

Krzysztof wrote: > When I use INSERT OR IGNORE, if insertion fail (record exists), > then sqlite3_last_insert_rowid does return nothing. If your unique key is the rowid, then you already know the ID that you tried to insert. If your unique key is not the rowid, then why do you need the rowid?

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

On 4 Jan 2013, at 9:55am, Krzysztof wrote: > When I use INSERT OR IGNORE, if insertion fail (record exists), > then sqlite3_last_insert_rowid does return nothing. Is exists similar > solution which: > 1. If insert success then return new rowid > 2. If insert fail (record exists)