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
