Re: Insert ... select ... On Duplicate Update Question
At 08:23 PM 7/20/2008, Perrin Harkins wrote: On Sun, Jul 20, 2008 at 12:12 AM, mos [EMAIL PROTECTED] wrote: Is there a way to get Insert ... select ... On Duplicate Update to update the row with the duplicate key? That's what it does. Why can't it do this? What makes you think it can't? - Perrin Perrin, I can't specify all of the columns in a Set statement in the OnDuplicate clause because I don't know what the column names are and there could be 100 columns. I'd like to use something like: insert into Table2 select * from table1 on duplicate key update; but this gives me a syntax error. Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 So it is looking for an Update expression. I'd like it to update all the columns in the Select statement to the row with the matching key. After all, this is what the statement was trying to do in the first place. I don't see why I have to explicitly specify all of the value assignments in the On Duplicate phrase over again. Mike MySQL 5.0.24 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert ... select ... On Duplicate Update Question
On Mon, Jul 21, 2008 at 11:44 AM, mos [EMAIL PROTECTED] wrote: I can't specify all of the columns in a Set statement in the OnDuplicate clause because I don't know what the column names are and there could be 100 columns. Write code to do it. There is no way around specifying the columns. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert ... select ... On Duplicate Update Question
At 11:00 AM 7/21/2008, Perrin Harkins wrote: On Mon, Jul 21, 2008 at 11:44 AM, mos [EMAIL PROTECTED] wrote: I can't specify all of the columns in a Set statement in the OnDuplicate clause because I don't know what the column names are and there could be 100 columns. Write code to do it. There is no way around specifying the columns. - Perrin Perrin, Ok thanks. I'll do that. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert ... select ... On Duplicate Update Question
So just use REPLACE instead of INSERT... http://dev.mysql.com/doc/refman/5.0/en/replace.html On Mon, Jul 21, 2008 at 11:44 AM, mos [EMAIL PROTECTED] wrote: At 08:23 PM 7/20/2008, Perrin Harkins wrote: On Sun, Jul 20, 2008 at 12:12 AM, mos [EMAIL PROTECTED] wrote: Is there a way to get Insert ... select ... On Duplicate Update to update the row with the duplicate key? That's what it does. Why can't it do this? What makes you think it can't? - Perrin Perrin, I can't specify all of the columns in a Set statement in the OnDuplicate clause because I don't know what the column names are and there could be 100 columns. I'd like to use something like: insert into Table2 select * from table1 on duplicate key update; but this gives me a syntax error. Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 So it is looking for an Update expression. I'd like it to update all the columns in the Select statement to the row with the matching key. After all, this is what the statement was trying to do in the first place. I don't see why I have to explicitly specify all of the value assignments in the On Duplicate phrase over again. Mike MySQL 5.0.24 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Help build our city at http://free-dc.myminicity.com !
Re: Insert ... select ... On Duplicate Update Question
At 12:16 PM 7/21/2008, you wrote: So just use REPLACE instead of INSERT... Sure, but a Replace will delete the existing row and insert the new one which means also maintaining the indexes. This will take much longer than just updating the existing row. Now if there were only a couple of rows then a Replace will work fine (but it would also execute Delete/Insert triggers if I had any). But I have 50 million rows and will need to update maybe 1/2% of those, all of those deletes and inserts will slow things down. Now logically I thought this should work: insert into Table2 select * from table1 on duplicate key update; I thought if MySQL found a duplicate key on the insert, it would automatically update the existing row that it found with the results from table1 if I left out the column expressions in the update clause. But apparently it doesn't work that way. It looks like I have to re-specify each of the column names in Table1 in the Update clause as a column assignment. I thought this was totally necessary because MySQL knew the column assignments for the original insert, why couldn't it pick up where it left off and use the existing row (specified by the duplicate key value it found). Either that or just force me to specify the key value assignment in the Update clause and not the whole column list which could be 100 columns. Mike http://dev.mysql.com/doc/refman/5.0/en/replace.htmlhttp://dev.mysql.com/doc/refman/5.0/en/replace.html On Mon, Jul 21, 2008 at 11:44 AM, mos mailto:[EMAIL PROTECTED][EMAIL PROTECTED] wrote: At 08:23 PM 7/20/2008, Perrin Harkins wrote: On Sun, Jul 20, 2008 at 12:12 AM, mos mailto:[EMAIL PROTECTED][EMAIL PROTECTED] wrote: Is there a way to get Insert ... select ... On Duplicate Update to update the row with the duplicate key? That's what it does. Why can't it do this? What makes you think it can't? - Perrin Perrin, I can't specify all of the columns in a Set statement in the OnDuplicate clause because I don't know what the column names are and there could be 100 columns. I'd like to use something like: insert into Table2 select * from table1 on duplicate key update; but this gives me a syntax error. Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 So it is looking for an Update expression. I'd like it to update all the columns in the Select statement to the row with the matching key. After all, this is what the statement was trying to do in the first place. I don't see why I have to explicitly specify all of the value assignments in the On Duplicate phrase over again. Mike MySQL 5.0.24 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert ... select ... On Duplicate Update Question
On Mon, Jul 21, 2008 at 2:43 PM, mos [EMAIL PROTECTED] wrote: I thought if MySQL found a duplicate key on the insert, it would automatically update the existing row that it found with the results from table1 if I left out the column expressions in the update clause. But apparently it doesn't work that way. It probably could, but it doesn't. With the availability of the information schema, it's easy to look up the columns in a table, so doing this from a program is relatively simple. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert ... select ... On Duplicate Update Question
On Sun, Jul 20, 2008 at 12:12 AM, mos [EMAIL PROTECTED] wrote: Is there a way to get Insert ... select ... On Duplicate Update to update the row with the duplicate key? That's what it does. Why can't it do this? What makes you think it can't? - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert ... select ... On Duplicate Update Question
Is there a way to get Insert ... select ... On Duplicate Update to update the row with the duplicate key? Otherwise I'll have to use Replace which is inefficient because it deletes the old duplicated row and then inserts the new row with the same key. I'd much rather have it update the existing row with the same existing keys, but use the new values in the Select statement. Why can't it do this? It would be much faster. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Odd Update Question.
Folks, Thanks for the 'help'. Oy. I figured it out from some help on the Lasso discussion list. All I had to do was properly address ALL the tables I wanted to touch. So this: UPDATE tbe_gallery SET tbe_gsa.gsa_paperprice = tbe_gallery.gallery_gsaprice_paper WHERE tbe_gallery.gallery_id = tbe_images.img_rel_id AND tbe_images.img_orig_filename = tbe_gsa.gsa_id Should have been This: UPDATE tbe_gallery, tbe_gsa, tbe_images SET tbe_gsa.gsa_paperprice = tbe_gallery.gallery_gsaprice_paper WHERE tbe_gallery.gallery_id = tbe_images.img_rel_id AND tbe_images.img_orig_filename = tbe_gsa.gsa_id And viola, it works! Miles.
Re: Odd Update Question.
http://dev.mysql.com/doc/refman/5.0/en/update.html See multiple table syntax. I have had issues with the syntax (IMHO), and is not available on 3.23 (I am a poor soul that still has to deal 3.23). If you have all the data and you can not figure out the syntax you can alternatively use INSERT... SELECT (and multi table works in 3.23) and recreate the tables. I generally like this approach because it is non destructive in that you still have the old table in case you have an error in your logic somwhere. On Wed, Feb 27, 2008 at 8:09 PM, m i l e s [EMAIL PROTECTED] wrote: Hi, I'm wondering if the following can be done UPDATE tbe_gallery SET tbe_gsa.gsa_paperprice = tbe_gallery.gallery_gsaprice_paper WHERE tbe_gallery.gallery_id = tbe_images.img_rel_id AND tbe_images.img_orig_filename = tbe_gsa.gsa_id Let me explain: I have 3 tables and only 1 of them has the correct data which I need to update the other two. The SQL statement above is based upon the following select statement below: SELECT tbe_gsa.gsa_id, tbe_gallery.gallery_id, tbe_gallery.gallery_title, tbe_gallery.gallery_price, tbe_gsa.gsa_sin, tbe_gsa.gsa_paperprice, tbe_gsa.gsa_canvasprice FROM tbe_gsa INNER JOIN tbe_images ON tbe_gsa.gsa_id = tbe_images.img_orig_filename INNER JOIN tbe_gallery ON tbe_images.img_rel_id = tbe_gallery.gallery_id ORDER BY gsa_id ASC This statement works just fine. However the table tbe_gsa contains the necessary column tbe_gsa.gsa_paperprice which has a match field of gsa_id, which matches a field in the images table called tbe_images.img_orig_filename, and the images table contains a match field called tbe_images.img_rel_id, which matches a field in the gallery table called tbe_galery.gallery_id. So my question is how do use the corresponding match fields to update the necessary fields so that... be_gsa.gsa_paperprice = tbe_gallery.gallery_gsaprice_paper Is my statement above anywhere close to what it should be Miles. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Odd Update Question.
Hi, I'm wondering if the following can be done UPDATE tbe_gallery SET tbe_gsa.gsa_paperprice = tbe_gallery.gallery_gsaprice_paper WHERE tbe_gallery.gallery_id = tbe_images.img_rel_id AND tbe_images.img_orig_filename = tbe_gsa.gsa_id Let me explain: I have 3 tables and only 1 of them has the correct data which I need to update the other two. The SQL statement above is based upon the following select statement below: SELECT tbe_gsa.gsa_id, tbe_gallery.gallery_id, tbe_gallery.gallery_title, tbe_gallery.gallery_price, tbe_gsa.gsa_sin, tbe_gsa.gsa_paperprice, tbe_gsa.gsa_canvasprice FROM tbe_gsa INNER JOIN tbe_images ON tbe_gsa.gsa_id = tbe_images.img_orig_filename INNER JOIN tbe_gallery ON tbe_images.img_rel_id = tbe_gallery.gallery_id ORDER BY gsa_id ASC This statement works just fine. However the table tbe_gsa contains the necessary column tbe_gsa.gsa_paperprice which has a match field of gsa_id, which matches a field in the images table called tbe_images.img_orig_filename, and the images table contains a match field called tbe_images.img_rel_id, which matches a field in the gallery table called tbe_galery.gallery_id. So my question is how do use the corresponding match fields to update the necessary fields so that... be_gsa.gsa_paperprice = tbe_gallery.gallery_gsaprice_paper Is my statement above anywhere close to what it should be Miles. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
Update question
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. 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 -- 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: On Duplicate Key Update question
Hi, From your query, understood that you want to retain old qty and new qty; result in another field. Try with, INSERT INTO TABLE1 (id,newqty) values (6,300) ON DUPLICATE KEY UPDATE totqty=oldqty+newqty, oldqty=newqty; Thanks, ViSolve DB Team - Original Message - From: Ed Reed [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, January 06, 2007 4:10 AM Subject: On Duplicate Key Update question I use On Duplicate Key Update a lot and I usually use it like this, Insert Into tablename (myID, Qty) Values (1034,15), (6,13), (5001,1), (145,20) On Duplicate Key Update Qty=Values(Qty); This works very well but now I'd like to do something a little different. I'd like to have a query like this but instead of replacing the value of the previous Qty I'd like it to take the old Qty and the new Qty and store the sum of the two values in the Qty field. Is this possible and can anyone tell me how? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
On Duplicate Key Update question
I use On Duplicate Key Update a lot and I usually use it like this, Insert Into tablename (myID, Qty) Values (1034,15), (6,13), (5001,1), (145,20) On Duplicate Key Update Qty=Values(Qty); This works very well but now I'd like to do something a little different. I'd like to have a query like this but instead of replacing the value of the previous Qty I'd like it to take the old Qty and the new Qty and store the sum of the two values in the Qty field. Is this possible and can anyone tell me how? Thanks
Re: On Duplicate Key Update question
Sorry for the premature question. I think I figured it out. On Duplicate Key Update Qty=Qty+Values(Qty); I haven't tested it yet but it makes sense that it'll work. Ed Reed [EMAIL PROTECTED] 1/5/07 2:40 PM I use On Duplicate Key Update a lot and I usually use it like this, Insert Into tablename (myID, Qty) Values (1034,15), (6,13), (5001,1), (145,20) On Duplicate Key Update Qty=Values(Qty); This works very well but now I'd like to do something a little different. I'd like to have a query like this but instead of replacing the value of the previous Qty I'd like it to take the old Qty and the new Qty and store the sum of the two values in the Qty field. Is this possible and can anyone tell me how? Thanks
Re: On Duplicate Key Update question
Ed Reed wrote: I use On Duplicate Key Update a lot and I usually use it like this, Insert Into tablename (myID, Qty) Values (1034,15), (6,13), (5001,1), (145,20) On Duplicate Key Update Qty=Values(Qty); This works very well but now I'd like to do something a little different. I'd like to have a query like this but instead of replacing the value of the previous Qty I'd like it to take the old Qty and the new Qty and store the sum of the two values in the Qty field. Is this possible and can anyone tell me how? Thanks doing it one record at a time I would do something like this... Insert Into tablename (myID, Qty) Values ($myID,$Qyt) On Duplicate Key Update Qty = Qty + $Qty you may also be able to use... On Duplicate Key Update Qty = Qty + Values(Qty) But I have never used that before so I'm not sure -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Probably naive update question
I have a field representing the chances the user has to get a password right, which is initially 3. I would like, if possible in a single query, to be able to decrement it if it's still 0 and return the value. Something like this: UPDATE table SET chances = IF(chances 0, chances - 1, 0) WHERE id = xxx SELECT chances FROM table WHERE id = xxx Is there some tidy way to do that with, say, a subquery (something to which I'm still quite new, having been stuck with MySQL 3 until recently)? I don't even know for certain that I have the IF syntax right, but I think I have. I'm using MySQL 5, btw. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Never trust a man who, when left alone in a room with a tea cosy, doesn't try it on. -- Billy Connolly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Probably naive update question
Chris, I'm not aware of a way to use ordinary SQL (insert, update) for this, but the use of a stored procedure would work for you. I've not done it with MySQL (never had a need) but did things like this extensively with Sybase. In rough terms: CREATE PROCEDURE sp_chances(IN xxx INT) RETURNS INT BEGIN UPDATE table SET chances = IF(chances 0, chances - 1, 0) WHERE id = xxx; SELECT chances FROM table WHERE id = xxx; END; Then you would execute this SQL: CALL sp_chances(xxx) and it should return the number of chances left for user id xxx, having decremented the counter as well (if 0). See http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html for more info Dan On 8/9/06, Chris Sansom [EMAIL PROTECTED] wrote: I have a field representing the chances the user has to get a password right, which is initially 3. I would like, if possible in a single query, to be able to decrement it if it's still 0 and return the value. Something like this: UPDATE table SET chances = IF(chances 0, chances - 1, 0) WHERE id = xxx SELECT chances FROM table WHERE id = xxx Is there some tidy way to do that with, say, a subquery (something to which I'm still quite new, having been stuck with MySQL 3 until recently)? I don't even know for certain that I have the IF syntax right, but I think I have. I'm using MySQL 5, btw. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Never trust a man who, when left alone in a room with a tea cosy, doesn't try it on. -- Billy Connolly -- 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: Probably naive update question
At 8:49 -0500 9/8/06, Dan Buettner wrote: Chris, I'm not aware of a way to use ordinary SQL (insert, update) for this, but the use of a stored procedure would work for you. I've not done it with MySQL (never had a need) but did things like this extensively with Sybase. In rough terms: CREATE PROCEDURE sp_chances(IN xxx INT) RETURNS INT BEGIN UPDATE table SET chances = IF(chances 0, chances - 1, 0) WHERE id = xxx; SELECT chances FROM table WHERE id = xxx; END; Then you would execute this SQL: CALL sp_chances(xxx) and it should return the number of chances left for user id xxx, having decremented the counter as well (if 0). Hi Dan Thanks for this, but it's clear to me that all the stored procedure is doing is running the two queries I was running otherwise. It would save me a couple of lines of PHP code, but is it really any more efficient? Especially as this will be on a /very/ small database (at least, by the standards of some of the people on this list!) and won't happen particularly often. It'll only get called of the user doesn't get the password right first time, which most of them will do - and there won't be many anyway, at least not at first. What I was really hoping for was some equivalent of mysql_insert_id(), but returning some other value from the last query. Not to worry - two quick queries it is... or maybe it would do me good to start learning about stored procedures. :-) Thanks again! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ 10 percent of computer users are Mac users, but remember, we are the top 10 percent. -- Douglas Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Probably naive update question
Agreed, in your case it may be 6 of one, half a dozen of the other. If you were calling a stored procedure to autheticate someone, as in CALL autheticate(username, password) then you could conceivably later alter your entire authentication database model without ever having to touch your application code. In theory, it's great. In practice, it is useful but not like sliced bread. Dan On 8/9/06, Chris Sansom [EMAIL PROTECTED] wrote: At 8:49 -0500 9/8/06, Dan Buettner wrote: Chris, I'm not aware of a way to use ordinary SQL (insert, update) for this, but the use of a stored procedure would work for you. I've not done it with MySQL (never had a need) but did things like this extensively with Sybase. In rough terms: CREATE PROCEDURE sp_chances(IN xxx INT) RETURNS INT BEGIN UPDATE table SET chances = IF(chances 0, chances - 1, 0) WHERE id = xxx; SELECT chances FROM table WHERE id = xxx; END; Then you would execute this SQL: CALL sp_chances(xxx) and it should return the number of chances left for user id xxx, having decremented the counter as well (if 0). Hi Dan Thanks for this, but it's clear to me that all the stored procedure is doing is running the two queries I was running otherwise. It would save me a couple of lines of PHP code, but is it really any more efficient? Especially as this will be on a /very/ small database (at least, by the standards of some of the people on this list!) and won't happen particularly often. It'll only get called of the user doesn't get the password right first time, which most of them will do - and there won't be many anyway, at least not at first. What I was really hoping for was some equivalent of mysql_insert_id(), but returning some other value from the last query. Not to worry - two quick queries it is... or maybe it would do me good to start learning about stored procedures. :-) Thanks again! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ 10 percent of computer users are Mac users, but remember, we are the top 10 percent. -- Douglas Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
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
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
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) 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: [SPAM] - concat string and update question - Found word(s) remove list in the Text body
Try this UPDATE people SETphone = CASE WHEN LEFT(phone,4) = '405_' THEN MID(phone,5,20) WHEN LEFT(phone,3) = '405' THEN MID(phone,4,20) ELSE phone END FROM people WHERE LEFT(phone,3) = '405' AND LENGTH(phone) 7; This way you don't accidentally replace '405' contained in the rest of the phone number. Also, if the phone numbers contain punctuation you will need to change the '7' in the LENGTH criteria. You will have to replace 'people' and 'phone' with the appropriate table and column name respectively. -Original Message- From: 2wsxdr5 [mailto:[EMAIL PROTECTED] Sent: Friday, January 06, 2006 8:09 PM To: mysql@lists.mysql.com Subject: [SPAM] - concat string and update question - Found word(s) remove list in the Text body I have a table of people and their phone numbers, some have the area code and others do not. Everyone in this table lives in the same area code, so I would like to remove the area code from the phone number field. Basically replace '(405)' or '405-' with '' is there an easy way to do that in a query with out writing code? I know how to do it with code but would like an easier way if some one knows the SQL better than I do. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- 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]
SELECT UPDATE question
I know this is possible but I'm not real sure of the command to use. I have 2 tables that are pretty much identical except for one column. What I want to do is moved data from one table column to the other table column based on a matching id number that is also a column in both tables called id. UPDATE table2 SET active = '1' WHERE table2.id = table1.id; is this the correct syntax? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update question
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? Thanks, Juan
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]
Update question
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? Jay Drake [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 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 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
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 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
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
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
UPDATE question, SQL syntax, etc.
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
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, 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 mysql- [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [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.
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 mysql- [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [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
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: 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
update question number in mysql
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
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 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
Update question
UPDATE returns the number of rows that were actually changed. How is the returned number of rows formatted for SQLFetch/SQLGet? Thanks Jeff Tanner Seattle, WA - 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
Update Question
How can i do an update a column in a table width the fields of another table i would like to do the folowing query: UPDATE enc_oper set estado = "N", quant_prod = 1 * opera.quantidade whe re encomenda = 20010004580 and posicao = 1 and opera.codigo = enc_prod.opera; But i dont know how to get the value from table opera. Can someone help me? - 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
update question
For example, I have a table value position 3001 1002 5003 2004 I have to update position ordered by value, and table must look like this value position 3003 1001 5004 2002 Is it possible to make such update in single query?
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
Update Question
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. Any help would be greatly appreciated. Thanks. -- Clarence Kwei [EMAIL PROTECTED] ScienCentral, Inc. (212) 244-9577 ext. 118 http://www.stn2.com http://www.sciencentral.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
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
