yes, I noted the syntax for UPDATE and was surprised that it wasn't accomodated.
I have searched high and low for signs that other people have come up against the same problem. There are many instances of people coming across it, but I haven't found a solution in any of the correspondence. I noted that on one mail list that someone opointed it out in 1999 and Monty said that he would be looking into it very soon. This dual update can be done in Access. I know this because I am converting a DB from Access to MySQL. I am having trouble converting all of the queries. This is because they contain things such as this along with 'nested selects' and the such like. T >From: "DL Neil" <[EMAIL PROTECTED]> >Reply-To: "DL Neil" <[EMAIL PROTECTED]> >To: "Richard Dobson" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> >Subject: Re: update from table x to table y >Date: Tue, 13 Nov 2001 21:13:19 -0000 > >=No there isn't, because: > >7.24 UPDATE Syntax >UPDATE [LOW_PRIORITY] [IGNORE] tbl_name > SET col_name1=expr1, [col_name2=expr2, ...] > [WHERE where_definition] > [ORDER BY ...] > [LIMIT #] > >- there is only 'space' for one tbl_name in the UPDATE clause. > >=I've had a quick look around. I'm sure someone on the list mentioned this >recently (but I can't find the >msg/must have deleted it). This sort of functionality might not be very far >away in the MySQL development >plan... > >=coming back to your original question: can this dual-table update actually >be done in Access? > >=coming back to my recent point, is the data properly normalised? I'm >having difficulty (don't I always?) in >visualising a reason for doing this. Would you like to discuss your >application and put some sample data in >front of us, in the hope that someone might be able to make a sensible (and >successful) suggestion? Also what >tool(s) are you using to access MySQL? > >=Sorry! >=dn > > > > Hi, thanks dn, > > so, to confirm, there is no MySQL to represent the following?: > > > > update Raw,Unresolved_Duplicates_perm set Raw.Inactivate = 1 where > > Raw.Key_num = Unresolved_Duplicates_perm.MinOfKey > > and Raw.Peak1=0 > > > > Thanks > > Rich > > > > >From: "DL Neil" <[EMAIL PROTECTED]> > > >Reply-To: "DL Neil" <[EMAIL PROTECTED]> > > >To: "Richard Dobson" <[EMAIL PROTECTED]>, ><[EMAIL PROTECTED]> > > >Subject: Re: update from table x to table y > > >Date: Tue, 13 Nov 2001 13:49:23 -0000 > > > > > >Duplicating values (cf keys) in tables involves de-normalisation and is > > >therefore not recommended. > > > > > >Your observation row cf column is correct. I felt the question was > > >sufficiently broad to risk interpreting > > >"update" as wider than "UPDATE", sorry - the other response seemed to >hit > > >that nail on the head (have deleted > > >it). > > > > > >If it doesn't suit, then another solution might be to use PHP to SELECT >the > > >data from table1 and then use a > > >second query to UPDATE table-2 SET table2-col = table1-col WHERE >table1-val > > >= table2-val (which, in its present > > >form, has the potential to modify numerous rows in one hit). > > > > > >=Regards, > > >=dn > > > > > > > > > > thanks for that, but that will add a row as opposed to updating a >column > > > > won't it? > > > > I don't want to insert a new row. > > > > All I want to do is update a value in table1 if it is present in >table2 > > > > > > > > cheers > > > > Rich > > > > > > > > >From: "DL Neil" <[EMAIL PROTECTED]> > > > > >Reply-To: "DL Neil" <[EMAIL PROTECTED]> > > > > >To: "Richard Dobson" <[EMAIL PROTECTED]>, > > ><[EMAIL PROTECTED]> > > > > >Subject: Re: update from table x to table y > > > > >Date: Tue, 13 Nov 2001 11:45:45 -0000 > > > > > > > > > > > Hi does anyone know of a way of taking some data from one table >and > > > > >updating > > > > > > another table with it? > > > > > > > > > > > > If MySQL doesn't support it i'm gonna have to go back to Access >or > > > > > > something! > > > > > > > > > > > > > > >Rich > > > > > > > > > >=only "go back" to Access if you're a glutton for punishment! > > > > > > > > > >=try MySQL/HTMLmanual/manual_Reference.html#INSERT_SELECT > > > > > > > > > >=dn > > > > > > > > > > > > > > > > > > > > >--------------------------------------------------------------------- > > > > >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 > > > > > > > > > > > > > > > > > _________________________________________________________________ > > > > Get your FREE download of MSN Explorer at > > >http://explorer.msn.com/intl.asp > > > > > > > > > > > > > > > > >--------------------------------------------------------------------- > > >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 > > > > > > > > > _________________________________________________________________ > > Get your FREE download of MSN Explorer at >http://explorer.msn.com/intl.asp > > > > > > --------------------------------------------------------------------- > > 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 > _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp --------------------------------------------------------------------- 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
