FW: [Zope-DB] Re: How do you do update with multiple tables

[garry saddington]

On Friday 30 April 2004 9:08 pm, Laura McCord wrote:
> I was thinking the same thing and I tried that but doesn't that mean
> that when I insert a new user I would have to know the pcid? When I
> enter the user info I want it to be easy enough for the user to be able
> to enter the name of the pc instead of the id. However, when I do the
> update there is no pcid in the users table that will trigger the
> users.pcname to update.
>
> Make sense?
>
> Thanks,
>  Laura
>
> -----Original Message-----
> From: zope-db-bounces at zope.org [mailto:zope-db-bounces at zope.org] On
> Behalf Of Sean Fulmer
> Sent: Friday, April 30, 2004 2:58 PM
> To: zope-db at zope.org
> Subject: [Zope-DB] Re: How do you do update with multiple tables
>
> Laura McCord wrote:
> > First of all, I am passing an argument 'pcname'.
> >
> > I have two tables that are linked by pcname
> >
> > Table Users        Table pcTable
> >
> > userid                 pcid
> > username     |-------- pcname
> > useremail    |         pcmake
> > useroffice   |         pcmodel
> > userpwd      |         pcserial
> > pcname--------
> >
> >
> > If I want to update the pcname in the pcTable then I want the pcname
> > in the users table to update as well. How is this done? This is what I
> >
> > have so far:
> >
> > update pcTable ,users
> > set
> > pcTable.pcname = '<dtml-var name="pcname" sql_quote>', pcTable.pcmake
> > = '<dtml-var name="pcmake" sql_quote>', pcTable.pcmodel = '<dtml-var
> > name="pcmodel" sql_quote>', pcTable.pcserial = '<dtml-var
> > name="pcserial" sql_quote>', pcTable.pcmhz = '<dtml-var name="pcmhz"
> > sql_quote>', pcTable.pcmbram = '<dtml-var name="pcmbram" sql_quote>',
> > pcTable.pc_oem_os = '<dtml-var name="pc_oem_os" sql_quote>',
> > pcTable.pc_oem_os_key = '<dtml-var name="pc_oem_os_key" sql_quote>',
> > pcTable.pc_ip_add = '<dtml-var name="pc_ip_add" sql_quote>',
> > users.pcTable = '<dtml-var name="pcname" sql_quote>'
> > where
> > pcname = '<dtml-var name="pcname" sql_quote>'
> >
> > Is there something that I am forgetting?
> >
> > Thanks,
> >  Laura
>
> Hard to tell what you're forgetting if you don't say what kind of
> problem you're having (ie a detailed error message)
>
> IMO, the correct thing to do would be add a pcid field to your user
> table. If you join your tables on pcid, then you only need to change
> pcname in one place.

Another respondent has pointed to the fact that you need to use foreign keys 
in your database to prevent redundancy. For the pc choice it would be 
possible to have a select box on your form where the pc to be selected comes 
from a ZSQL method but the value sent back to the database is an ID.
regards
garry