Re: Update question
On Wednesday 25 April 2007 23:14, you wrote: > try this: > > > update table1, table2 > set table1.value = table2.value > where table1.id = table2.id Thanks for the replies... It was late evening when I tried to figure out how to do this. Today I found the answer myself, which is exactly as described above. Lesson learned: Get a good night sleep and then try to figure out how to do things... ;-) -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update question
On Wed, April 25, 2007 23:10, Jørn Dahl-Stamnes wrote: > Please, I nedd help!! > > I have two tabels: > > table1: > id > value > > table2: > id > value > > Both tables has a lot of records with identical IDs. I need to update the > table1.value with the table2.value where the id are identical. update table1 t1, table2 t2 set t1.value=t2.value where t1.id=t2.id; > > But I cannot find any UPDATE query that can do this in a single operation. > Anyone that can give me a suggestion? > > I'm using MySQL 4.1.8 > -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE question
Cummings, Shawn (GNAPs) wrote: If I have 4 Fields (FIELD1, FIELD2, FIELD3 & FIELD4) I can do this easily; UPDATE TABLE_NAME SET FIELD4 = FIELD1; But -- how do I do it so that FIELD4 = FIELD1 & FIELD2 ??? I can't seem to find any examples online. Maybe it's just too early in the morning - I'm drawing a blank! ;) This depends on the data types, if you want to set FIELD4 to be FIELD1 + FIELD2 with integers: UPDATE table_name SET FIELD4 = (FIELD1 + FIELD2); If they are strings: UPDATE table_name SET FIELD4 = CONCAT(FIELD1,' ',FIELD2); If you are swapping values: UPDATE table_name SET FIELD4 = (@tmp:=FIELD4), FIELD4 = FIELD1, FIELD1 = @tmp; Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE question
On 5/3/06, Barry <[EMAIL PROTECTED]> wrote: Cummings, Shawn (GNAPs) schrieb: > > If I have 4 Fields (FIELD1, FIELD2, FIELD3 & FIELD4) > > I can do this easily; > > UPDATE TABLE_NAME SET FIELD4 = FIELD1; > > But -- how do I do it so that FIELD4 = FIELD1 & FIELD2 ??? I can't seem > to find any examples online. Maybe it's just too early in the morning - > I'm drawing a blank! ;) FIELD4 = FIELD1 & FIELD 2? Field 4 should be field 1 AND field 2? Can't decide which one field4 should get? :P Well, every child learns that "1 and 1 is 2" ;-P -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE question
Cummings, Shawn (GNAPs) schrieb: If I have 4 Fields (FIELD1, FIELD2, FIELD3 & FIELD4) I can do this easily; UPDATE TABLE_NAME SET FIELD4 = FIELD1; But -- how do I do it so that FIELD4 = FIELD1 & FIELD2 ??? I can't seem to find any examples online. Maybe it's just too early in the morning - I'm drawing a blank! ;) FIELD4 = FIELD1 & FIELD 2? Field 4 should be field 1 AND field 2? Can't decide which one field4 should get? :P -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UPDATE question
Shawn, Perhaps : UPDATE TABLE_NAME SET FIELD4 = concat(FIELD1,FIELD2); -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 03, 2006 9:33 AM To: Mysql General (E-mail) Subject: UPDATE question If I have 4 Fields (FIELD1, FIELD2, FIELD3 & FIELD4) I can do this easily; UPDATE TABLE_NAME SET FIELD4 = FIELD1; But -- how do I do it so that FIELD4 = FIELD1 & FIELD2 ??? I can't seem to find any examples online. Maybe it's just too early in the morning - I'm drawing a blank! ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update question
At 18:41 -0400 2/28/04, Juan E Suris wrote: Here's my table definition: CREATE TABLE `files` ( `id` int(11) NOT NULL auto_increment, `checksum` char(32) NOT NULL default '', `size` bigint(20) NOT NULL default '0', PRIMARY KEY (`id`), KEY `checksum` (`checksum`(8)) This table is used to store information on files. When I delete a file, instead of deleting the row, I set checksum to the empty string and size to 0. When I want to add a new file, I try the following query, and if it returns 0 rows affected, I will then try a regular insert: UPDATE files SET checksum='[md5 of file]', size=[size of file] WHERE checksum='' AND size=0 ORDER BY id LIMIT 1 The idea is to try and reuse the id's of deleted files, and if there aren't any, then I do an INSERT to create a new id. My concern is that if I have many clients doing this, that updates from different clients may catch the same row and I would lose some data. Am I correct? No. Thanks, Juan -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update question
At 5:14 PM -0500 9/18/03, <[EMAIL PROTECTED]> wrote: I'm not quite sure why I haven't run across this in the past, but now that I have I am stumped. I am needing to update a table based on criteria found in it and one other table, but I am uncertain how to proceed. If I had subselects I would run the query as follows, I believe: UPDATE suppliercatlink SET suppliercatlink.catid=124 WHERE suppliercatlink.supid IN (SELECT supplier.id FROM supplier WHERE supplier.company_name LIKE %exteri%) AND suppliercatlink.catid=10 ; Knowing that this is not an option I figure maybe I could join the tables in my UPDATE statement like: UPDATE suppliercatlink, supplier SET suppliercatlink.catid=124 WHERE supplier.company_name LIKE '%brick%' AND supplier.id=suppliercatlink.supid AND suppliercatlink.catid=10 ; Looking at the documentation it appears this will not work, at least not with 3.23 which I am currently running. It appears that something of this nature would work if I upgraded to 4.0.4, but I really prefer to update mySQL before or after a project, not right in the middle of it. Can anyone help me figure out a way around this problem? If you don't want to update to MySQL 4 (which will indeed allow you to run your second UPDATE above, then you'll need to code the equivalent logic in an application. Select the ID list from supplier for those records that need updating, then use them to construct a set of UPDATE statements for the suppliercatlink table. Jay Drake [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update question
On Sun, 2 Mar 2003 17:58:13 -0500 "Tore Bostrup" <[EMAIL PROTECTED]> wrote: > Personally, I usually like Reference books better than "Idiot's Guide" > books. And my favorite reference handbook for the SQL language (ANSI > SQL-92 standard) is > > Martin Gruber, SQL Instant Reference (SYBEX). There is (was) at > least a > 2nd edition available. > > No fluff, just standard syntax and a good description of the key > things you need to know for writing SQL queries. > > You'll still need to check the MySQL documentation to find out what > is/isn't supported, what differes froim the standard, and what > specific functions are available, etc. > > HTH, > Tore. Thanks, after reviewing my 'library' I find the books I have with mysql info are PHP books that have a chapter or two on MySQL. So, I'll have to buy a SQL book specifically. Regards, Chip > - Original Message - > From: "chip wiegand" <[EMAIL PROTECTED]> > To: "Tore Bostrup" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Sunday, March 02, 2003 4:25 PM > Subject: Re: update question > > > > On Sun, 2 Mar 2003 02:06:40 -0500 > > "Tore Bostrup" <[EMAIL PROTECTED]> wrote: > > > > > I assume you are storing your dates in a char/varchar column - not > > > a good choice to start with... :-< > > > > yes, varchar. I'm still learning this stuff, and experimenting with > > it. > > > > > Assuming all the values are supposed to be stored as MM-DD-YY > > > (anothoer marginal choice, but the problem may not rear its head > > > again for another 96+ years), you can do the following: > > > > heh, heh, I don't think I'll be around another 96 years to find out. > > Lets see, I would be 139 years old. Probably wouldn't be pushing too > > many keys on the keyboard at that age. This particular > > database/tables are not for business use, just my own learning. > > > > > UPDATE mytable > > > SET mydatestr = LEFT(mydatestr, LENGTH(mydatestr) - 3) > > > WHERE mydatestr LIKE '__-__-__-__' > > > > Thanks for the help, worked great. I didn't know about the > > underscore being a wildcard character. > > I should look for a better MySQL book, the ones I have don't cover > > that info. Any suggestions for one that does? > > -- > > Chip > > > > > HTH, > > > Tore. > > > > > > - Original Message - > > > From: "chip wiegand" <[EMAIL PROTECTED]> > > > To: "mysql" <[EMAIL PROTECTED]> > > > Sent: Sunday, March 02, 2003 1:46 AM > > > Subject: update question > > > > > > > > > > I need to make a change to a field in a table and don't know how > > > > to write the correct sql statement. I made an error and now have > > > > a date field with the year repeated twice - 01-01-03-03 - there > > > > are aproximately 100 rows like this, and maybe 20 or so that are > > > > formatted properly. How can I remove the last 3 characters while > > > > leaving other rows that do not have this problem alone? (other > > > > than manaully editing each row of course) > > > > Thanks, > > > > Chip W. > > > > www.wiegand.org > > > > > > > > --- > > > > -- 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 > > - 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
Personally, I usually like Reference books better than "Idiot's Guide" books. And my favorite reference handbook for the SQL language (ANSI SQL-92 standard) is Martin Gruber, SQL Instant Reference (SYBEX). There is (was) at least a 2nd edition available. No fluff, just standard syntax and a good description of the key things you need to know for writing SQL queries. You'll still need to check the MySQL documentation to find out what is/isn't supported, what differes froim the standard, and what specific functions are available, etc. HTH, Tore. - Original Message - From: "chip wiegand" <[EMAIL PROTECTED]> To: "Tore Bostrup" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Sunday, March 02, 2003 4:25 PM Subject: Re: update question > On Sun, 2 Mar 2003 02:06:40 -0500 > "Tore Bostrup" <[EMAIL PROTECTED]> wrote: > > > I assume you are storing your dates in a char/varchar column - not a > > good choice to start with... :-< > > yes, varchar. I'm still learning this stuff, and experimenting with it. > > > Assuming all the values are supposed to be stored as MM-DD-YY > > (anothoer marginal choice, but the problem may not rear its head again > > for another 96+ years), you can do the following: > > heh, heh, I don't think I'll be around another 96 years to find out. > Lets see, I would be 139 years old. Probably wouldn't be pushing too > many keys on the keyboard at that age. This particular database/tables > are not for business use, just my own learning. > > > UPDATE mytable > > SET mydatestr = LEFT(mydatestr, LENGTH(mydatestr) - 3) > > WHERE mydatestr LIKE '__-__-__-__' > > Thanks for the help, worked great. I didn't know about the underscore > being a wildcard character. > I should look for a better MySQL book, the ones I have don't cover that > info. Any suggestions for one that does? > -- > Chip > > > HTH, > > Tore. > > > > - Original Message - > > From: "chip wiegand" <[EMAIL PROTECTED]> > > To: "mysql" <[EMAIL PROTECTED]> > > Sent: Sunday, March 02, 2003 1:46 AM > > Subject: update question > > > > > > > I need to make a change to a field in a table and don't know how to > > > write the correct sql statement. I made an error and now have a date > > > field with the year repeated twice - 01-01-03-03 - there are > > > aproximately 100 rows like this, and maybe 20 or so that are > > > formatted properly. How can I remove the last 3 characters while > > > leaving other rows that do not have this problem alone? (other than > > > manaully editing each row of course) > > > Thanks, > > > Chip W. > > > www.wiegand.org > > > > > > --- > > > -- 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
On Sun, 2 Mar 2003 02:06:40 -0500 "Tore Bostrup" <[EMAIL PROTECTED]> wrote: > I assume you are storing your dates in a char/varchar column - not a > good choice to start with... :-< yes, varchar. I'm still learning this stuff, and experimenting with it. > Assuming all the values are supposed to be stored as MM-DD-YY > (anothoer marginal choice, but the problem may not rear its head again > for another 96+ years), you can do the following: heh, heh, I don't think I'll be around another 96 years to find out. Lets see, I would be 139 years old. Probably wouldn't be pushing too many keys on the keyboard at that age. This particular database/tables are not for business use, just my own learning. > UPDATE mytable > SET mydatestr = LEFT(mydatestr, LENGTH(mydatestr) - 3) > WHERE mydatestr LIKE '__-__-__-__' Thanks for the help, worked great. I didn't know about the underscore being a wildcard character. I should look for a better MySQL book, the ones I have don't cover that info. Any suggestions for one that does? -- Chip > HTH, > Tore. > > - Original Message - > From: "chip wiegand" <[EMAIL PROTECTED]> > To: "mysql" <[EMAIL PROTECTED]> > Sent: Sunday, March 02, 2003 1:46 AM > Subject: update question > > > > I need to make a change to a field in a table and don't know how to > > write the correct sql statement. I made an error and now have a date > > field with the year repeated twice - 01-01-03-03 - there are > > aproximately 100 rows like this, and maybe 20 or so that are > > formatted properly. How can I remove the last 3 characters while > > leaving other rows that do not have this problem alone? (other than > > manaully editing each row of course) > > Thanks, > > Chip W. > > www.wiegand.org > > > > --- > > -- 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
I assume you are storing your dates in a char/varchar column - not a good choice to start with... :-< Assuming all the values are supposed to be stored as MM-DD-YY (anothoer marginal choice, but the problem may not rear its head again for another 96+ years), you can do the following: UPDATE mytable SET mydatestr = LEFT(mydatestr, LENGTH(mydatestr) - 3) WHERE mydatestr LIKE '__-__-__-__' HTH, Tore. - Original Message - From: "chip wiegand" <[EMAIL PROTECTED]> To: "mysql" <[EMAIL PROTECTED]> Sent: Sunday, March 02, 2003 1:46 AM Subject: update question > I need to make a change to a field in a table and don't know how to > write the correct sql statement. I made an error and now have a date > field with the year repeated twice - 01-01-03-03 - there are > aproximately 100 rows like this, and maybe 20 or so that are formatted > properly. How can I remove the last 3 characters while leaving other > rows that do not have this problem alone? (other than manaully editing > each row of course) > Thanks, > Chip W. > www.wiegand.org > > - > 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
> -Original Message- > From: Jörgen Winqvist [mailto:jorgen@;winqvist.net] > > I need to let the values in two columns change place with each other. > I've tried to "update xxx set a=b, b=a" but that doesn't work > (b=a uses > the "new" a). Here's a cute trick for swapping two numbers without using a temporary variable: set a=a-b, b=b+a, a=b-a I don't know how applicable it is to your query, but it might be worth considering. - 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
Jörgen, Friday, November 01, 2002, 11:06:38 AM, you wrote: JW> I need to let the values in two columns change place with each other. JW> I've tried to "update xxx set a=b, b=a" but that doesn't work (b=a uses JW> the "new" a). If you want to change place for all values, you can just rename columns :-) You can also add third column, f.e. "c" UPDATE xxx SET c=a, a=b, b=c; and then drop column "c"; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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.
.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
Re: update question number in mysql
Hi, Steven and christopher, Thanks a lot for your help. I think i have to store the question number in table. my talbe questionbank store the questions related more than one course. every course has six question sets. so i use question set id and question number as primary key. There is one problem is every time i delete one question for example delete question 5, i have to update all the question numbers after question 5 in this question set.(for example if there is 10 question in this set, i delete question 5 i have to update question numbers for question 6,7 8 9,10 to 5,6,7,8,9) it will take some time. but i think this is for administrator so maybe speed is not very serious issue. Thanks steven for his recommendation for the transactional table i will take a look at it. This is great mailing list. Thanks again bin cai --- Steven Hajducko <[EMAIL PROTECTED]> wrote: > On Fri, 2002-04-12 at 13:52, Christopher Thompson > wrote: > > You shouldn't be storing the questio number at > all. You have denormalised > > your database and this is why you are experiencing > this problem. > > Maybe I don't understand bin's problem, but storing > the question # > wouldn't seem to be de-normalizing. It would only > matter on how he > plans on storing it. > > It would seem to matter on a few things - > > 1) Is he planning on storing more than 1 exam? > 2) Can an exam really have the same question #, > twice? > If yes, then - > Are the questions split upon sections of the exam? > Are the questions the same, but have subsections? > ( 1a, 1b ? ) > > From the looks of it, he doesn't seem to want to > store more than 1 exam > anyways. If he did, he could use a 2 field primary > key of exam name and > question number. There just isn't enough > information on what he wants > to do. > > > > > Instead, store a unique identifier with each one > (an 'id' field, > > autonumbering) and then, when you pull back all > the questions, order it by > > this ID field. Your questions won't have numbers > assigned to them but you > > can do that in whatever app uses the questions. > > Why not use the question number? It's unique per > exam. (Or should be?). > > I can see the case of doing this would help in his > problem of the first > and second query, but what if someone enters > questions out of order? If > he deletes a question, then tries to re-insert it, > it would end up being > the last question in the exam. > > You might want to look into using a transactional > table bin. Then, you > could BEGIN the change, then after your two queries > are both completed, > commit the change. Therefore, if your computer > crashed in the middle of > the query, the table would revert to the old form. > > > Of course, the truth is that you _can_ do what you > want just using SQL. But > > trust me, you don't want to. It would be at least > O(n^2), I think, and would > > be a pain to write. > > > > On Friday 12 April 2002 2:38 pm, bin cai wrote: > > > Hello, verybody, > > > I am afraid to send my question again. > > > > > > i created a table in mysql database system to > store > > > exam questions which has the following column > fields: > > > questionnumber(integer),questionbody(String), > answers > > > (String) and etc. > > > My problem is if i delete one row(question > record) how > > > can iupdate the questionnumber in the easiest > way > > > which > > > means mysql can do for me. Is it possible? or i > have > > > to write a method to update questionnumber. > > > if In second case i got another worry. for > example, > > > i wrote two querys,one is to delete this > question > > > record another query is to update the > > > questionnumber,that is , all records whose > > > questionnumbers are greater than the > questionnumber of > > > the deleted question should deducted by 1. if at > the > > > time After the first query is executed > successfully > > > and the second query is being executed, the > computer > > > is crashed. the second query will be rolled > back. so > > > the question is deleted but the questionnumber > is not > > > updated. this will result in a mess. > > > If anybody can give me some hint i will > appreicate > > > very much > > > Have a good weekend > > > bin > > > > > > > > > > __ > > > Music, Movies, Sports, Games! > http://entertainment.yahoo.ca > > > > > > > - > > > 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: >
Re: update question number in mysql
On Fri, 2002-04-12 at 13:52, Christopher Thompson wrote: > You shouldn't be storing the questio number at all. You have denormalised > your database and this is why you are experiencing this problem. Maybe I don't understand bin's problem, but storing the question # wouldn't seem to be de-normalizing. It would only matter on how he plans on storing it. It would seem to matter on a few things - 1) Is he planning on storing more than 1 exam? 2) Can an exam really have the same question #, twice? If yes, then - Are the questions split upon sections of the exam? Are the questions the same, but have subsections? ( 1a, 1b ? ) >From the looks of it, he doesn't seem to want to store more than 1 exam anyways. If he did, he could use a 2 field primary key of exam name and question number. There just isn't enough information on what he wants to do. > > Instead, store a unique identifier with each one (an 'id' field, > autonumbering) and then, when you pull back all the questions, order it by > this ID field. Your questions won't have numbers assigned to them but you > can do that in whatever app uses the questions. Why not use the question number? It's unique per exam. (Or should be?). I can see the case of doing this would help in his problem of the first and second query, but what if someone enters questions out of order? If he deletes a question, then tries to re-insert it, it would end up being the last question in the exam. You might want to look into using a transactional table bin. Then, you could BEGIN the change, then after your two queries are both completed, commit the change. Therefore, if your computer crashed in the middle of the query, the table would revert to the old form. > Of course, the truth is that you _can_ do what you want just using SQL. But > trust me, you don't want to. It would be at least O(n^2), I think, and would > be a pain to write. > > On Friday 12 April 2002 2:38 pm, bin cai wrote: > > Hello, verybody, > > I am afraid to send my question again. > > > > i created a table in mysql database system to store > > exam questions which has the following column fields: > > questionnumber(integer),questionbody(String), answers > > (String) and etc. > > My problem is if i delete one row(question record) how > > can iupdate the questionnumber in the easiest way > > which > > means mysql can do for me. Is it possible? or i have > > to write a method to update questionnumber. > > if In second case i got another worry. for example, > > i wrote two querys,one is to delete this question > > record another query is to update the > > questionnumber,that is , all records whose > > questionnumbers are greater than the questionnumber of > > the deleted question should deducted by 1. if at the > > time After the first query is executed successfully > > and the second query is being executed, the computer > > is crashed. the second query will be rolled back. so > > the question is deleted but the questionnumber is not > > updated. this will result in a mess. > > If anybody can give me some hint i will appreicate > > very much > > Have a good weekend > > bin > > > > > > __ > > Music, Movies, Sports, Games! http://entertainment.yahoo.ca > > > > - > > 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 number in mysql
You shouldn't be storing the questio number at all. You have denormalised your database and this is why you are experiencing this problem. Instead, store a unique identifier with each one (an 'id' field, autonumbering) and then, when you pull back all the questions, order it by this ID field. Your questions won't have numbers assigned to them but you can do that in whatever app uses the questions. Of course, the truth is that you _can_ do what you want just using SQL. But trust me, you don't want to. It would be at least O(n^2), I think, and would be a pain to write. On Friday 12 April 2002 2:38 pm, bin cai wrote: > Hello, verybody, > I am afraid to send my question again. > > i created a table in mysql database system to store > exam questions which has the following column fields: > questionnumber(integer),questionbody(String), answers > (String) and etc. > My problem is if i delete one row(question record) how > can iupdate the questionnumber in the easiest way > which > means mysql can do for me. Is it possible? or i have > to write a method to update questionnumber. > if In second case i got another worry. for example, > i wrote two querys,one is to delete this question > record another query is to update the > questionnumber,that is , all records whose > questionnumbers are greater than the questionnumber of > the deleted question should deducted by 1. if at the > time After the first query is executed successfully > and the second query is being executed, the computer > is crashed. the second query will be rolled back. so > the question is deleted but the questionnumber is not > updated. this will result in a mess. > If anybody can give me some hint i will appreicate > very much > Have a good weekend > bin > > > __ > Music, Movies, Sports, Games! http://entertainment.yahoo.ca > > - > 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
>Hi. > >On Thu, Jan 18, 2001 at 05:39:25PM -0500, [EMAIL PROTECTED] wrote: > > > > How can I do the following: > > > > update location set location.city_id = location_city.city_id where > > location.city = location_city.name; > > > > I want to update one field in a table with values from another table. Is > > this possible within MySQL or do I need to write some code to do this. > >It is not yet possible (but planned for 4.0), so you have to write >some work-around for it. Sir, for a possible work-around, go to my website at http://users.starpower.net/rjhalljr. Click on MySQL on the side bar, and then click on MySQL SQL. Look for the Update using other tables topic in the Strictly SQL section. You will have to insert the data into a temp table, and then REPLACE from the temp table. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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
Hi. On Thu, Jan 18, 2001 at 05:39:25PM -0500, [EMAIL PROTECTED] wrote: > > How can I do the following: > > update location set location.city_id = location_city.city_id where > location.city = location_city.name; > > I want to update one field in a table with values from another table. Is > this possible within MySQL or do I need to write some code to do this. It is not yet possible (but planned for 4.0), so you have to write some work-around for it. Bye, Benjamin. - 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
