From: Mathias (mfatenefree.fr)
Date: Sun Jul 03 2005 - 02:25:33 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Selon Don Parris <webdevmatheteuo.org>:

> 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/mysql?unsub=mfatenefree.fr
>
>
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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]