Use insert or ignore: Insert or ignore into table values (list of bound values); Select idfield from table where naturalkeyfield = [refererence to appropriate bound value];
And execute both statements together. You will be returned the id. Example: Create table data ( Id integer primary key, Value text not null collate nocase unique ); Then Insert or ignore into data (value) values (:value); Select id from data where value = :value; And bind your value to the :value named parameter. You will get back the id or nothing. If nothing the record does not exist and could not be created. Otherwise you will get back the explicitly named rowid for the row. The explicitly named rowid is always unique and incrementing. Why do you need the extra overhead of the rowid always being greater than any rowid ever used in the table, which is what AUTOINCREMENT means. Also, why are your text fields not declared with COLLATE NOCASE? Is case important? Is so, then why are you creating a NOCASE index? I have never seen in 35 years a text field where case is important but can be sometimes be ignored. Either case matters everywhere or it matters nowhere, and declaring the attribute to not care about case ensures that case is ignored everywhere so you do not have to remember to use "collate nocase" everytime you want case to be ignored (such as to get the index to be used). If you want to have a database from which you can retrieve the complete list of Components (ie, individual floppy drives, motherboards, etc); the assembly/product (ie the box); and the components in the box, then you are missing a table that links the components in the assemblies ... > -----Original Message----- > From: [email protected] [mailto:sqlite-users- > [email protected]] On Behalf Of Stephen Chrzanowski > Sent: Saturday, 14 September, 2013 08:39 > To: General Discussion of SQLite Database > Subject: [sqlite] INSERT or REPLACE alternatives > > As per usual for me, I had written a novel out, before hitting SEND, to > ask > a question about INSERT or REPLACE alternatives. I worked out a > solution, > so deleted the draft. You can thank my coffee to blood ratio. ;) > > Even though my code works, I figured I'd maybe post a question for > alternative theories on how to accomplish an INSERT OR REPLACE while > keeping an auto-numbered field the same. Here's what I've done; > > *Project:* > I'm in the process of auditing several computers, notebooks, and > monitors > for an upcoming internal company auction. I'm building a very simple > application that will keep track of categories and products belonging to > that category. Within reason, each part of the computer (Product table) > will be associated with a URL so the other employees can go to a website > for more product info if available. As it stands right now, I'm > entering > the different components of each computer (Motherboard, CPU, memory, > etc) > individually, and another table, not introduced here, will tie > everything > together into a single asset. The asset being all the components of a > single computer identified by a unique tag number. Out of the 10 > machines > I've audited so far, I'm dealing with about 5 different motherboards, 3 > different types of CPUs, 4 different video cards, and several > configurations of memory and drives, and of course, each computer is > differently configured, even though I'm only working with two case > styles > for the desktop computers. What I intend for this program to do is, > once I > finish entering the details of each asset, the software will quickly > compile the list of parts of each asset with URLs so that I can just > copy/paste the data into the auction software. I'll end up going to > each > manufacturers website and find the link for the part in question, but > hey... For some knowing how fast the CPU is is enough, while for others, > knowing the nitty gritty of the build is essential. For those that want > to > know, it helps. ;) > > *SQL: * > CREATE TABLE [Categories] ( > [CatID] INTEGER PRIMARY KEY AUTOINCREMENT, > [Name] CHAR); > > CREATE UNIQUE INDEX [idxCatName] ON [Categories] ([Name] COLLATE > NOCASE); > > CREATE TABLE [Products] ( > [ProdID] INTEGER PRIMARY KEY AUTOINCREMENT, > [CatID] INTEGER, > [ProdName] CHAR, > [Link] CHAR); > > CREATE INDEX [fkCatID] ON [Products] ([CatID]); > > CREATE UNIQUE INDEX [fkUniqueProdName] ON [Products] ([ProdName]); > > *PCode: > * > - select CatID from Categories where Upper(Name)=Upper(?); -- ? = Text > field on form in my GUI > - Check to see the number of rows returned > --- If zero, do the insert, get the last value inserted into a local > variable (CatID) -- I tend to name my application variables after my SQL > fields > --- If not zero, put returned value into local variable (CatID) > - update Categories set Name=? where CatID=[local_CatID]; --- This > ensures > that the camel case for the category name is updated as last specified > in > the form field when the detail is saved. > > ***Question for you: > * > Other than the single optimization of verifying if the SQL version of > 'Name' matches byte for byte what the form field contains prior to the > Update statement, how else could you perform the equivalent of an > "INSERT > OR REPLACE" without the delete part of REPLACE? I'm looking at > something > that is 100% internal to SQLite that can be executed in "one to a few" > statements, or some other pseudo code to do the same job. The other > thing > I'm looking to do is try to make a procedure that'll be robust enough so > that I can spew the code out into a global unit that I can plop into > other > projects that'll do the INSERT OR REPLACE WITHOUT DELETE. > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
