In answer to your question - your statement does not work becuase mysql does
not allow joins in update statements.
imho, the fact that mysql does not support joins in UPDATE and DELETE
statements is by far its greatest weakness. I have posted a couple of
messages to this group to see what creative work-arounds others have used to
fill this gaping hole in functionality. Alas, no one has felt like sharing
their thoughts on this subject.
> -----Original Message-----
> From: Philip Montgomery [mailto:[EMAIL PROTECTED]]
> Sent: Sunday, September 23, 2001 10:35 PM
> To: [EMAIL PROTECTED]
> Subject: Update problem
>
>
> I am having problems getting an update to work correctly. Assume
> that I have two tables, employee and office. Employee has two
> fields, employee_id and employee_name, and office has three
> fields, office_id, office_number, & employee_id.
>
> I have been using inner join to link the tables together when
> pulling information out of the database, but I can't get join to
> work with update.
>
> Say I want to update the office_number for a particular employee.
> I would have to get the employee_id from the employee table by
> referencing the employee_name in order to update the row with the
> corresponding employee_id in the office table.
>
> Thus far I have been trying statements like this:
>
> update office inner join employee using (employee_id) set
> office_number="XXX" where employee_name="XXXXXX";
>
> I know that I could work around this via Perl, but I would rather
> have mysql do the work.
>
> Any help would be appreciated,
>
> Thanks,
>
> Phil
>
>
> Get 250 color business cards for FREE!
> http://businesscards.lycos.com/vp/fastpath/
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
