Re: UPDATE question, SQL syntax, etc.
.ben, Monday, May 20, 2002, 3:15:11 PM, you wrote: b> hi. i'm new to the list and have only been playing with mySQL for a few b> weeks now, i have a question regrading the syntax of an UPDATE statement - i b> hope nobody minds me asking. b> i want to uopdate a table with the data from another, i've written the b> following: b> update trackinfo SET trackinfo.postcode = newtrackinfo.postcode FROM b> trackinfo, newtrackinfo WHERE trackinfo.telephone = newtrackinfo.telephone; b> it should update trackinfo with the postcodes from newtrackinfo, as long as b> the telephone numbers match - but i get an errror - it complains about the b> 'FROM...' onwards. b> am i missign something simple? Yeah. Description of UPDATE syntax you can find in our manual: http://www.mysql.com/doc/U/P/UPDATE.html However, MySQL doesn't support mutli-table updates yet. b> cheers, b> .ben -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - 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
RE: UPDATE question, SQL syntax, etc.
cheers for the responses. i'll have to find another way, but thanks. .b > -Original Message- > From: Nick Stuart [mailto:[EMAIL PROTECTED]] > Sent: 20 May 2002 13:46 > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: UPDATE question, SQL syntax, etc. > > > There is no from clause in the update syntax. However, I'm not sure how > you would update a tablethe way you are trying to. I'm sure it > can be done, and your SQL looks > correct besides the fromclause. Just take that out and see if > what it says. > > -Nick > > > hi. i'm new to the list and > > have only been playing with mySQL for a > > few weeks now, i have a question regrading the > > syntax of an UPDATE > > statement - i hope nobody minds me asking. > > > > i want to uopdate a > > table with the data from another, i've written the > > following: > > > > update trackinfo > > SET trackinfo.postcode = newtrackinfo.postcode FROM > > trackinfo, newtrackinfo > WHERE > trackinfo.telephone = > > newtrackinfo.telephone; > > > > it should update > trackinfo > with the postcodes from newtrackinfo, as > > long as the telephone numbers > match - but i get an > errror - it > > complains about the 'FROM...' onwards. > > > > am i missign > something > simple? > > > > cheers, > > > > .ben > > > > > > - > > 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 > - 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
Re: UPDATE question, SQL syntax, etc.
There is no from clause in the update syntax. However, I'm not sure how you would update a tablethe way you are trying to. I'm sure it can be done, and your SQL looks correct besides the fromclause. Just take that out and see if what it says. -Nick > hi. i'm new to the list and have only been playing with mySQL for a > few weeks now, i have a question regrading the syntax of an UPDATE > statement - i hope nobody minds me asking. > > i want to uopdate a table with the data from another, i've written the > following: > > update trackinfo SET trackinfo.postcode = newtrackinfo.postcode FROM > trackinfo, newtrackinfo WHERE trackinfo.telephone = > newtrackinfo.telephone; > > it should update trackinfo with the postcodes from newtrackinfo, as > long as the telephone numbers match - but i get an errror - it > complains about the 'FROM...' onwards. > > am i missign something simple? > > cheers, > > .ben > > > - > 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 To > unsubscribe, e-mail > 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
RE: UPDATE question, SQL syntax, etc.
[snip] update trackinfo SET trackinfo.postcode = newtrackinfo.postcode FROM trackinfo, newtrackinfo WHERE trackinfo.telephone = newtrackinfo.telephone; [/snip] http://www.mysql.com/doc/U/P/UPDATE.html MySQL does not support sub-queries, such as the one you are attempting here. (the "FROM" on..) You could try INSERT...SELECT http://www.mysql.com/doc/I/N/INSERT_SELECT.html or REPLACE...SELECT http://www.mysql.com/doc/R/E/REPLACE.html The REPLACE is probably what you're looking for, but be careful, "REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a unique index, the old record is deleted before the new record is inserted. " HTH! Jay "We are all apparently 'net' literate, why don't we use it to get the information we need? After all, isn't that what a Google search is all about?" - 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