Duncan, Thanks for your comments. My db is small and the records are at less than 200. The tables number 7 and the colums 18 not counting the primary auto increment. The UPDATE method I ended up using took about 1 second for the one colum I moved or copied. I am just using this db as "read only" so my needs are quite different from the norm probably. The SQL commands will eventually get sorted out and the usual rules apply to how fast I learn. :-) First the word games. Sometimes a command or syntax is not intuitive so even if I looked at the word "Update" it didn't sound like what I was looking for at the time. I was thinking "copy" or "cut and paste" as it seemed feasible because a spreadsheet can do that so why couldn't a db app? :-) It will all be water under the bridge some day...and I will be smarter too. Thanks, schemer
On 3/7/2015 7:18 PM, Darren Duncan wrote: > On 2015-03-07 9:59 AM, Simon Slavin wrote: >> On 7 Mar 2015, at 4:42pm, Dave <theschemer at cox.net> wrote: >> >>> I am fairly new at this although I have wanted to learn and tried >>> again and again...But I have a problem. I created a database and >>> probably did it wrong and I am trying to fix it. I made a database >>> with 7 tables in it all with a primary key and a record ID that >>> matches the primary key. Now when trying to use the database I see >>> that I should have made 1 table with all the related data (I think) >>> and am trying to copy one column of data at a time to the "main" >>> table. Can that be done and if so how? >> >> Without going into your situation in detail, I have a suggestion >> which may help you approach the problem another way. The SQLite >> shell tool has a '.dump' command which turns a database into SQL >> commands, and a '.read' command which uses the commands to create >> schema and data in a new database. >> >> So dump the database into a text file. Then you can use editing >> tools (usually global find-and-replace) mess with the text file so >> that all the inserting is done to the same table. Then you can >> create your new database by reading the altered text file. > > Frankly the idea (proposed by Simon here) of solving this by dumping > everything to a text file and manipulating it there with editing tools > sounds abysmal to me. > > The only time one might consider that reasonable is if the total > number of records is just a handful and you're essentially just > re-entering them from scratch. > > Once you've already got your data in SQLite, the best general solution > by far is to use SQL to manipulate it; if you can't, you've already lost. > > What you want to do is create new table(s) with the new format you > want, and then do INSERT INTO <new> SELECT FROM <old> such that the > SELECT easily and reliably does all the hard work of collecting up all > the data from the old tables and rearranging it into the new format. > Depending on the complexity of the task, you may also create temporary > tables for intermediate stages of the processing. > > Solving the problem with the likes of SQL UPDATE is hard, but using > SELECT is easy. > > By a similar token, I believe SQL is often the best place to clean up > data from external sources. Create temporary tables that are very lax > in format and constraints that take the external data as pristine as > possible, load into those, and then use SELECTs/etc to derive cleaner > versions from those into the final tables (or other intermediaries), > and you can use the SQL powers to filter or compensate for dirty data > etc. Especially useful for dealing with duplicate data in the source, > find or handle with SELECT GROUP BY etc rather than trying conditional > INSERT logic or what have you. > > -- Darren Duncan > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
