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
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: 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 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
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, 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?
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
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
