AFAIK, you can't do 'nested insert', or, insert to multiple tables in one call. Not from a single command line, or, from a view. You're pretty much stuck with updating one table at a time.
It would be nice, however, problems can come up with a many-to-many situation where the engine isn't sure what to push to the database due to FK constraints. The view may exclude vital information. On Wed, Feb 18, 2015 at 6:38 AM, Flakheart <gary.flakheart at gmail.com> wrote: > Apologies in advance folks, please forgive the question and the formatting. > > I have around 650,000 recipes and through lots of study have more or less > determined that the table structures below will give me a reasonable > normalised database. > > That is not to say that it is perfect, but it is a start. > > Querying such a thing isn't a problem, I have played with queries to work > on > existing normalised databases and 'got the picture'. > > My problem is that: Given all the data on a form from which I gather what > to > insert, how would a complex insert statement look to update all the various > tables to keep the relationships. I have no idea how it would look?? > Haven't > seen any examples on this, just queries. > > I can deal with single table inserts but I think this would be some sort of > nested insert statement? > > If anyone could help me with an example or at least point me to where I > could see examples of updating normalised databases, I would be grateful. > > Thank you. > recipes table recipe_id ; Unique record number > recipe_name ; Recipe title > recipe_version ; Revision number > recipe_owner ; Recipe owner (home > reference) > recipe_author ; Who wrote the recipe > recipe_source ; Where the recipe was > sourced from > recipe_copyright ; Recipe owner isn't > necessarily copyright holder > recipe_lastupdate ; Last time this was > updated > recipe_favourite ; Marked as a > favourite > recipe_deleted ; Marked as deleted, > to > be purged > recipe_description ; Specific recipe > description > recipe_notes ; General notes > recipe_servings ; Number of servings > recipe_peparationtime ; Preparation time > recipe_cookingtime ; Cooking time > recipe_totaltime ; Total time > > ingredients table ingredient_id ; > ingredient_name ; > ingredient_price ; > > recipe_ingredients table recipe_id ; > ingredient_id ; > ingredient_amount ; > > recipe_instructions table recipe_id ; > recipe_step ; > step_description ; > > category table category_id ; > category_name ; > > recipe_category table recipe_id ; > category_id ; > > picture table picture_id ; > picture_name ; > picture_url ; > > recipe_pictures table recipe_id ; > picture_id ; > > > > > -- > View this message in context: > http://sqlite.1065341.n5.nabble.com/Complex-insert-query-to-normalised-database-tp80590.html > Sent from the SQLite mailing list archive at Nabble.com. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
