> I think no-one's replied because the answer is really simple
Oh I don't know Jay, I've seen some threads 4-5 responses deep to issues far
simpler than this.
> create a recordset using Select for the records you are interested
> in (using your joins), then use that recordset to perform your
> UPDATE or DELETE.
I understand that you could use REPLACE with this newly created recordset to
simulate the UPDATE w/JOIN, but your "it's so simple" response glosses over
the details of what to do with the delete. The only way I've conceived is
to carry an "isdeleted" field which is set using the replace command, then
used in where clause of a DELETE. Of course I may be missing a simpler or
better way to do this... which is why I made my original post.
> I have to
> deal with the same situation Phil describes quite often, but I've never
> missed the 'join in update' function.
Well I'm happy for you but did you ever stop and consider that what you do
with your database may not necessarily be representative of what everyone
does? In my situation, one that is certainly not unique, I have tables with
100+ fields and many million rows. The SELECT INTO/REPLACE FROM and SELECT
INTO/REPLACE FROM/DELETE WHERE approaches to UPDATE FROM and DELETE FROM are
an order of magnitude less efficient. This inefficiency coupled with the
need (using myisam) to lock the entire table during updates makes for some
not-so-simple design challenges.
In closing, I offer a couple rhetorical questions:
1. Who is the bigger idiot... The person who asks a naive question or the
person who, upon hearing a question he knows the answer to, assumes the
asker is an idiot?
2. Does your condescending attitude win you as many points with your users
as it did with me?
Will French
> -----Original Message-----
> From: Jay Fesco [mailto:[EMAIL PROTECTED]]
> Sent: Monday, September 24, 2001 8:30 AM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED];
> [EMAIL PROTECTED]
> Subject: RE: Update problem
>
>
> >
> > 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.
> >
>
> Will (and Phil)
>
> I think no-one's replied because the answer is really simple - create a
> recordset using Select for the records you are interested in (using your
> joins), then use that recordset to perform your UPDATE or DELETE.
> I have to
> deal with the same situation Phil describes quite often, but I've never
> missed the 'join in update' function. Not that it wouldn't be
> nice, but the
> workaround is not rocket surgery.
>
> Jay Fesco
>
>
---------------------------------------------------------------------
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
