Selon Don Parris <[EMAIL PROTECTED]>: > O.k., this question is more about the best way to run a multi-table insert > on a MySQL DB (4.0.18), given a console based interface (Python 2.3.x under > SUSE Linux 9.2). My guess is that this would be a bit easier with a GUI, > where I would have widgets to assign to various functions. However, I am > currently building a console app. The GUI will come later. > > When I add a person to the DB, the main table impacted is called 'person'. > However, a few other tables are affected as well - entity, address, > affiliation, and aux_mbr. For example, the person table uses the keys from > the others: > > person.affil_id = affil.affil_id, person.ent_id = entity.ent_id, etc. > > Somehow, the program will need to get the foreign keys from the other > tables, and insert that into the person table. Technically, the program > won't be very likely to know what the current primary_key is for each table. > > The 2 options I see so far are: > (1) run insert queries on each table, ignoring the foreign key fields, and > then running an update query on person to add the foreign keys from the > respective tables: > insert into person values (PK, 'blah', 'blab') > insert into entity values (PK, 'bleep', 'blip') > update person set ent_id=1 where person_id=1 #'1' should be a variable > > (2) run a query when the input form is launched to determine the current > max value of the foreign keys, then use that info to automatically add the > foreign keys into the person table: > select entity_id MAX from entity > ### run Python input form, ent_id is passed to the appropriate input > statement ### > insert into person tuple #(PK, ent_id, 'blah', 'blab') > > The commands here aren't intended to be precise, but rather to help paint > the picture. > > A link to a previous thread or documentation on this would be fine. I don't > mind doing the reading - but my Google search seems to turn up more info > about multi-table deletes than inserts. I realize that MySQL doesn't > support a single multi-table insert function, but are the two approaches I > see good, bad, common?? > > Thanks, > Don > -- > evangelinux GNU Evangelist > http://matheteuo.org/ http://chaddb.sourceforge.net/ > "Free software is like God's love - you can share it with anyone anytime > anywhere." > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > Hi, here are some links : http://www.php-resource.de/forum/showthread/t-54709.html http://dev.mysql.com/doc/mysql/en/insert.html http://bugs.mysql.com/bug.php?id=8732 http://bugs.mysql.com/bug.php?id=1980 http://archives.neohapsis.com/archives/mysql/2004-q3/3604.html http://forums.devshed.com/archive/t-51965/Insert-Into http://lists.nyphp.org/pipermail/talk/2003-September/005768.html http://dev.mysql.com/doc/mysql/en/innodb-multi-versioning.html http://www.issociate.de/board/post/26176/Ref:_WCL302_Subject:_UPDATE_multi-table_current_column_value_error.html
Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
