RE: I seem to have lost a table somehow :-(
Does "repair table" work in this case? --- "Logan, David (SST - Adelaide)" <[EMAIL PROTECTED]> wrote: > Hi Vicki, > > If you have a backup of the .MYD file, you should be > able to copy this > over the existing one and run myisamchk -r to > rebuild the index. > > Regards > > David Logan > Database Administrator > HP Managed Services > 148 Frome Street, > Adelaide 5000 > Australia > > +61 8 8408 4273 - Work > +61 417 268 665 - Mobile > +61 8 8408 4259 - Fax > > > -Original Message- > From: Vicki Brown [mailto:[EMAIL PROTECTED] > Sent: Thursday, 20 January 2005 8:49 AM > To: Dathan Pattishall; mysql@lists.mysql.com > Subject: RE: I seem to have lost a table somehow :-( > > At 3:21 PM -0800 1/18/05, Dathan Pattishall wrote: > >Somehow your table was zero'ed out, i.e. TRUNCATE / > DROP-CREATE. > > > >MYD - data of the table > >MYI - index of the table > >Frm - TABLE definition > > > >I hope you have a backup. > > Ewwch. > I may have a backup. I definitely have the data. > Unfortunately, there > doesn't > seem to be an "easy" way to put the data back into > the db. > > On the other hand, it's not my weblog, it's seen > very little activity > over > its lifetime, the last entry was September and the > owner is temporarily > "off > the Internet". So it couldn't have happened to a > less important > database. > > Still. I wonder what crashed. > -- > Vicki Brown ZZZJourneyman > Sourceror: > SF Bay Area, CAzz |\ _,,,---,,_ > Scripts & Philtres > http://www.cfcl.com zz /,`.-'`'-. ;-;;,_Code, > Doc, Process, QA > http://cfcl.com/vlb |,4- ) )-,_. ,\ ( `'-'Perl, > Unix, Mac OS X, WWW > '---''(_/--' `-'\_) > ___ > > -- > 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] > > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to change mysqldump output txt file format?
Thanks Jim. Great suggestions.
I will try both to see which one is better for my
case.
Appreciated your help.
Monet
--- Jim Grill <[EMAIL PROTECTED]> wrote:
> > Yeah. I am moving data from mysql server to sql
> > server.
> > Because I have single quote in some strings, it
> > generated errors when I ran mysqldump scripts in
> sql
> > server to import data in.
> > i.e. strings Here's, Martin's ,... caused trouble.
> > Does that mean sql didn't recognize escaped single
> > quote?
> > Thanks,
> > Monet
> >
> I *think* sql server escapes single quotes *only*
> when two appear together.
> So two single quotes in a row equals one single
> quote. Brilliant, eh? ...To
> be a fly on the wall when that decision was made.
> :-) I think that is
> actually the ANSI SQL standard for escaping single
> quotes and not just an M$
> thing. It works in MySQL too by the way. Try it:
>
> INSERT INTO testing
> values('Here''s','Martin''s','mom''s');
>
> same as
>
> INSERT INTO testing
> values('Here\'s','Martin\'s','mom\'s');
>
> I suppose you could try to open the dump file in an
> editor like vi or
> notepad or some other editor that has a search and
> replace and replace \'
> with '' and see what happens. vi: :%s/\\'/''/g
>
> The alternative would be to use mysqdump with -T
> option and use the
> options --fields-terminated-by=,
> --fields-enclosed-by=, --fields-optionally-
> enclosed-by=, --fields-escaped-by=, and
> --lines-terminated-by= to make a csv
> file. You can use BCP or MS DTS to load a csv into
> sql server tables. There
> may be another sql server equivelant to LOAD DATA
> INFILE that can accept csv
> or tsv. Who knows???
>
> Good luck,
>
> Jim Grill
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
>
http://lists.mysql.com/[EMAIL PROTECTED]
>
>
__
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to change mysqldump output txt file format?
Yeah. I am moving data from mysql server to sql server. Because I have single quote in some strings, it generated errors when I ran mysqldump scripts in sql server to import data in. i.e. strings Here's, Martin's ,... caused trouble. Does that mean sql didn't recognize escaped single quote? Thanks, Monet --- [EMAIL PROTECTED] wrote: > Jim - He needs to change the format because he isn't > exporting from one > MySQL database to another His destination > database doesn't like the > escaped single quotes. > > Here is the manual page for mysqldump: > http://dev.mysql.com/doc/mysql/en/mysqldump.html > > Is there nothing you can do with > --fields-terminated-by=... > --fields-enclosed-by=... > --fields-optionally-enclosed-by=... > --fields-escaped-by=... > --lines-terminated-by=... > > to get what you want from mysqldump? > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > "Jim Grill" <[EMAIL PROTECTED]> wrote on > 09/24/2004 02:35:40 PM: > > > > Hello everyone, > > > > > > In mysqldump output txt file, all datetime, > varchar, > > > text fields value are surrounding by single > quotes. Is > > > there any way that the single quotes can be > replaced > > > by double quotes in the txt file? > > > Furthermore, if you have a single quote in text > field, > > > it will automatically replaced by \'. But I like > to > > > keep it in the way it input. > > > > > > > There is no way to change the use of single quotes > in a standard dump. > The > > only time you have a choice is when you use the -T > option, which creates > a > > tab or csv type dump files depending on options > present on the command > line. > > You could use this option if you wanted. The only > draw back is that > > mysqdump -T must be run on the same machine as the > server. > > > > However, why is the presents of escaped single > quotes a problem? They > are > > only escaped to let MySQL know to treat them as > literal single quotes > and > > not string delimiters. They do not actually get > inserted into your table > > with the slashes. > > > > Regards, > > > > Jim Grill > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to change mysqldump output txt file format?
Hello everyone,
In mysqldump output txt file, all datetime, varchar,
text fields value are surrounding by single quotes. Is
there any way that the single quotes can be replaced
by double quotes in the txt file?
Furthermore, if you have a single quote in text field,
it will automatically replaced by \. But I like to
keep it in the way it input.
For instances,
--
-- Dumping data for table 'week'
--
INSERT INTO week VALUES ('2004-03-23
10:13:00','3015','201','2003-06-13', 'coach
children\'s league ')
I want the output looks like:
INSERT INTO week VALUES (2004-03-23
10:13:00,3015,201,2003-06-13, coach children's
league)
So, is there any way to define mysqldump output file
format?
Thanks,
Monet
__
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to count columns in SQL?
Hi, everyone, I have a sql question here, please dont ignore it and help please. I know one can use describe table in mysql to get number of columns. Is there any way to do so in SQL? I have some tables on SQL sever 2000. I use Access 2000 Link table function to link to the server. And I want to count columns not rows since I want to know how many fields in a table. Any advise? Thanks. Monet __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: IN operator
Great explanison. I guest this is the point. after each time the table crashed during such process, it always followed a error message saying something about the memory violation. Thanks a lot. your idea also open my mind and deeper my understanding about mysql. Monet --- Dathan Vance Pattishall <[EMAIL PROTECTED]> wrote: > IN is a fantastic operator, but there are some > limitations especially memory > wise. > > Check out this algorithm > > Say you're using an Integer with an average of 8 > digits, i.e. in the 100s > millions, now you send an in list of say 20 of these > 8 digits numbers. Since > the data is passed to mysql as a string, the parser > has to allocate memory > for 160 bytes (20 * 8 bytes) + 19 bytes for each > comma. 339 bytes BAH that's > nothing right? Well, these are bytes allocated > outside of a key buffer, thus > if your key buffer is set to 1.9 GB on a 32 bit > system, your application has > many of these IN list passed to it, mysql will crash > because it just hit the > 2 GB limit. > > Does this explain your issue, no not necessarily but > it's good to add and > might explain some weird experiences. > > > DVP > ---- > Dathan Vance Pattishall > http://www.friendster.com > > > > -Original Message- > > From: Monet [mailto:[EMAIL PROTECTED] > > Sent: Monday, September 13, 2004 10:22 AM > > To: [EMAIL PROTECTED] > > Cc: [EMAIL PROTECTED] > > Subject: Re: IN operator > > > > Hi there, > > I used very likely statement last week to update > one > > table. My IN value is around 20. I checked the > manual > > and there is nothing about any limitation on IN > > values. > > However, when i was running it, it worked very > well > > sometimes, while sometimes, the query crashed in > the > > middle and i have to REPAIR table. I've not figure > out > > the reason of the crash yet. but i think you > should be > > aware of it. > > > > Monet > > > > --- Oliver Hirschi <[EMAIL PROTECTED]> wrote: > > > > > Hi people > > > > > > Due to MySQL does not support "inner-selects", I > > > generate a string (I > > > programm java-client) with the values I used in > an > > > IN-operator for an > > > update onto a mySQL database. > > > > > > The statement looks like this: > > > UPDATE layer SET State=1 > > > WHERE fpObjectID IN > (1,3,4,5,20,34,56,24,56,11,45) > > > > > > Now, the question came up if there is a maximum > of > > > values or length in > > > an IN operator which can used on mySQL? > > > > > > Does anybody know something about that? > > > > > > Thanks & Regards > > > -- > > > Oliver Hirschi > > > http://www.FamilyHirschi.ch > > > > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > > > > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > > > > > ___ > > Do you Yahoo!? > > Declare Yourself - Register online to vote today! > > http://vote.yahoo.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] > > ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IN operator
Hi there, I used very likely statement last week to update one table. My IN value is around 20. I checked the manual and there is nothing about any limitation on IN values. However, when i was running it, it worked very well sometimes, while sometimes, the query crashed in the middle and i have to REPAIR table. I've not figure out the reason of the crash yet. but i think you should be aware of it. Monet --- Oliver Hirschi <[EMAIL PROTECTED]> wrote: > Hi people > > Due to MySQL does not support "inner-selects", I > generate a string (I > programm java-client) with the values I used in an > IN-operator for an > update onto a mySQL database. > > The statement looks like this: > UPDATE layer SET State=1 > WHERE fpObjectID IN (1,3,4,5,20,34,56,24,56,11,45) > > Now, the question came up if there is a maximum of > values or length in > an IN operator which can used on mySQL? > > Does anybody know something about that? > > Thanks & Regards > -- > Oliver Hirschi > http://www.FamilyHirschi.ch > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
state my question more clearly Re: WHY this query keeps failure?
Yes, you're right. Let me explain it more clearly. Before UPDATE, there are 45 records in table "temp" and I updated 9 of them. Mysql returns how many rows were affected which is 9 rows. Then, I opened the table temp and found that table is empty!No records at all. Therefore, that is why I feel so wired. after a simple update, all records has been erased. does anyone have same problem before? Thanks, Monet --- Rhino <[EMAIL PROTECTED]> wrote: > > - Original Message - > From: "Monet" <[EMAIL PROTECTED]> > To: "mysql" <[EMAIL PROTECTED]> > Sent: Thursday, September 09, 2004 2:13 PM > Subject: WHY this query keeps failure? > > > > Hello, > > > > I was working on a table, doing a simple update on > > table. Query is: > > Update temp > > SET Q1 = 14, > > REVIEWCOMMENTS = > > CASE WHEN REVIEWCOMMENTS='WHO2' THEN '' > > WHEN REVIEWCOMMENTS LIKE '%,WHO2' THEN > TRIM(TRAILING > > ',WHO2' FROM REVIEWCOMMENTS) > > WHEN REVIEWCOMMENTS LIKE 'WHO2,%' THEN > TRIM(LEADING > > 'WHO2,' FROM REVIEWCOMMENTS) > > ELSE > > REPLACE(REVIEWCOMMENTS, 'WHO2,', '') > > END > > WHERE QID IN > > (3029,3041,3053,3076,3120,3121,3128,3133,3134); > > > > It runs well, shows how many rows was affected. > Then I > > did query to pull out all updated records: > > select qid, qd5,q1, reviewcomments > > from temp > > where qid IN > > (3029,3041,3053,3076,3120,3121,3128,3133,3134) > > order by qid asc; > > > > There is no records return. The table is empty. > > > Do you mean that your *result set* from the query is > empty? Or that the > *table* you are reading from (temp) is empty? You > said 'table' but I *think* > you mean 'result set', right? If temp is empty, your > result set from the > Select will certainly be empty; that should be > obvious: the question is WHY > temp is empty. > > Your table, temp, should not be empty as a result of > your update statement > because Update does not remove rows and your Update > didn't change the 'qid' > value. If Update changed 9 rows and MySQL told you > that 9 rows were changed, > you should still have at least those 9 rows in the > table after the update > has completed. You can verify that by doing: > > select count(*) from temp; > > immediately after running the update. If it returns > a value of 0, your table > is empty. Otherwise there are rows in the table. > > > This happened second time. So I'm wondering it > might > > have some problem with my query. > > > I don't see anything in the Update or the Select > that explains this problem. > > Rhino > > __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
WHY this query keeps failure?
Hello, I was working on a table, doing a simple update on table. Query is: Update temp SET Q1 = 14, REVIEWCOMMENTS = CASE WHEN REVIEWCOMMENTS='WHO2' THEN '' WHEN REVIEWCOMMENTS LIKE '%,WHO2' THEN TRIM(TRAILING ',WHO2' FROM REVIEWCOMMENTS) WHEN REVIEWCOMMENTS LIKE 'WHO2,%' THEN TRIM(LEADING 'WHO2,' FROM REVIEWCOMMENTS) ELSE REPLACE(REVIEWCOMMENTS, 'WHO2,', '') END WHERE QID IN (3029,3041,3053,3076,3120,3121,3128,3133,3134); It runs well, shows how many rows was affected. Then I did query to pull out all updated records: select qid, qd5,q1, reviewcomments from temp where qid IN (3029,3041,3053,3076,3120,3121,3128,3133,3134) order by qid asc; There is no records return. The table is empty. This happened second time. So Im wondering it might have some problem with my query. Thanks a lot Monet __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table fixed BUT...Re: Table crashed! Please help
Bingo. Yeah, that works very well. Really appreciated. BTW, still curious about the reason of duplicate.:) Any thoughts? Monet --- Jim Grill <[EMAIL PROTECTED]> wrote: > > Hello there, > > > > I tried "REPAIR TABLE" to recovery the crushed > temp > > table and that works. Thanks a lot. > > > > But, after reparation, there are some duplicated > > records generated. Is that because the temp table > has > > no primary key? Or is because the table was > crushed in > > the middle of query? > > Anyway, I tried to delete the duplicated records. > BUT, > > I can't. if I just simply delete one of duplicate > > record, then another one is deleted as well. For > > instance, there are two duplicate record with qid > = > > 3170, I delete one of them from table, confirm the > > delete, refresh table, another one disappeared > too. > > Does anyone has any idea about what happened and > what > > should I do to delete duplicate records? > > > > Many thanks for any advise. > > Monet > > > Try: > > SELECT COUNT(*) FROM table WHERE id=[your duplicate > value]; > > This will show you how many dups there are... so > let's say there were 7... > > DELETE FROM table WHERE id=[your duplicate value] > LIMIT 6; > > Now you have one left. > > I hope that helps. > > Jim Grill > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do you Yahoo!? Y! Messenger - Communicate in real time. Download now. http://messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table fixed BUT...Re: Table crashed! Please help
Hello there, I tried REPAIR TABLE to recovery the crushed temp table and that works. Thanks a lot. But, after reparation, there are some duplicated records generated. Is that because the temp table has no primary key? Or is because the table was crushed in the middle of query? Anyway, I tried to delete the duplicated records. BUT, I cant. if I just simply delete one of duplicate record, then another one is deleted as well. For instance, there are two duplicate record with qid = 3170, I delete one of them from table, confirm the delete, refresh table, another one disappeared too. Does anyone has any idea about what happened and what should I do to delete duplicate records? Many thanks for any advise. Monet --- "V. M. Brasseur" <[EMAIL PROTECTED]> wrote: > The manual knows all: > > http://dev.mysql.com/doc/mysql/en/REPAIR_TABLE.html > > Cheers, > > --V > > Monet wrote: > > I was working on a table, doing some simple update > on > > table, query is like: > > Update temp > > SET Q1 = 14, > > REVIEWCOMMENTS = CASE WHEN > REVIEWCOMMENTS='WHO2' > > THEN '' > > WHEN REVIEWCOMMENTS LIKE > > '%,WHO2' THEN TRIM(TRAILING ',WHO2' FROM > > REVIEWCOMMENTS) > > WHEN REVIEWCOMMENTS LIKE > > 'WHO2,%' THEN TRIM(LEADING 'WHO2,' FROM > > REVIEWCOMMENTS) > > ELSE > REPLACE(REVIEWCOMMENTS, > > 'WHO2,', '') > > END > > WHERE QID IN > > (3029,3041,3053,3076,3120,3121,3128,3133,3134); > > > > It runs well. Then I did query to pull out all > updated > > records: > > select qid, qd5,q1, reviewcomments > > from temp > > where qid IN > > (3029,3041,3053,3076,3120,3121,3128,3133,3134) > > order by qid asc; > > > > there is no records return. Then I opened temp > table > > found that there is no records at all. But temp > tables > > should contain 67 records. > > I dont know what happened. Those two queries are > so > > normal and ran several times today. > > Since I have dumped file, I tried to recovery by > run > > dumped sql to restore data into temp table. But > then > > an error message pop up said something like Table > > handler got some problem. > > So I cant recovery my temp table in that way. > Further > > more I cant close Navicat (one mysql interface). > It > > always got an error pop up saying something like > > memory access violate. > > > > I restart my computer, reopen temp table, error > > message like this 1016 cant open file > temp.MYI. > > (error 145) showed up and my table lost > everything, > > no data, no columns. > > > > I really have no any experience on this kind of > > situation, please help. Many thanks and much > > appreciated. > > > > Monet > > > > > > > > > > > > __ > > Do you Yahoo!? > > Yahoo! Mail - 50x more storage than other > providers! > > http://promotions.yahoo.com/new_mail > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table crashed! Please help
I was working on a table, doing some simple update on table, query is like: Update temp SET Q1 = 14, REVIEWCOMMENTS = CASE WHEN REVIEWCOMMENTS='WHO2' THEN '' WHEN REVIEWCOMMENTS LIKE '%,WHO2' THEN TRIM(TRAILING ',WHO2' FROM REVIEWCOMMENTS) WHEN REVIEWCOMMENTS LIKE 'WHO2,%' THEN TRIM(LEADING 'WHO2,' FROM REVIEWCOMMENTS) ELSE REPLACE(REVIEWCOMMENTS, 'WHO2,', '') END WHERE QID IN (3029,3041,3053,3076,3120,3121,3128,3133,3134); It runs well. Then I did query to pull out all updated records: select qid, qd5,q1, reviewcomments from temp where qid IN (3029,3041,3053,3076,3120,3121,3128,3133,3134) order by qid asc; there is no records return. Then I opened temp table found that there is no records at all. But temp tables should contain 67 records. I dont know what happened. Those two queries are so normal and ran several times today. Since I have dumped file, I tried to recovery by run dumped sql to restore data into temp table. But then an error message pop up said something like Table handler got some problem. So I cant recovery my temp table in that way. Further more I cant close Navicat (one mysql interface). It always got an error pop up saying something like memory access violate. I restart my computer, reopen temp table, error message like this 1016 cant open file temp.MYI. (error 145) showed up and my table lost everything, no data, no columns. I really have no any experience on this kind of situation, please help. Many thanks and much appreciated. Monet __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can append word into varchar column in Update statement?
Hi all, I though it is impossible to do that but I like check with you guys in case it is just because I never heard it. I have a table with a text column. Is there any way I can append some word into this field when I update the table? For instance, Table temp, column ReviewComments, data type of ReviewComments is varchar. Value in ReviewComments is PSRC. After update, value in ReviewComments should be PSRC, WHC Can I do that by using UPDATE SET . Thanks a lot. Monet ___ Do you Yahoo!? Win 1 of 4,000 free domain names from Yahoo! Enter now. http://promotions.yahoo.com/goldrush -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about Update multiple columns at once
I thought about this method before. But since there
are 6 sets of rows I want to update in one table and
they are common at some level, I am wondering whether
there is more efficient way to do it.
Thanks,
--- Michael Stassen <[EMAIL PROTECTED]>
wrote:
> Assuming that column Q3F in your example is really
> Q2F, the query is doing
> exactly what you told it to do. Rows which match
> your WHERE clause are
> being updated according to your SET clause.
>
> As I understand you, there are two different sets of
> rows you wish to
> update. The first set are the rows which match
>
>Q1A = 1 AND Q1E = 1 AND Q1F = 1 AND (Q1G IN ('',
> 'NA'))
>
> while the second set are the rows which match
>
>Q2A = 1 AND Q2E = 1 AND Q2F = 1 AND Q2G IN ('',
> 'NA'))
>
> If you wanted to do the same thing to both sets, one
> update query would make
> sense, but you don't. You want to make one set of
> changes to the first set
> of rows, and a different set of changes to the
> second set of rows. I think
> that calls for two updates:
>
>UPDATE temp
>SET
> Q1E = 6,
> Q1F = 5,
> Q1G = 999
>WHERE Q1A = 1 AND Q1E = 1 AND Q1F = 1 AND (Q1G IN
> ('', 'NA'));
>
>UPDATE temp
>SET
> Q2E = 6,
> Q2F = 5,
> Q2G = 999
>WHERE Q2A = 1 AND Q2E = 1 AND Q2F = 1 AND (Q2G IN
> ('', 'NA'));
>
> Note that, as a bonus, doing it this way eliminates
> the need for the IFs in
> the SET clauses, because their conditions are
> guaranteed to be met by rows
> which match the WHERE clauses.
>
> Michael
>
> Monet wrote:
>
> > Im trying to update multiple columns at once but
> > cannt do it in an efficient way.
> > What I am trying to do is:
> > Update table temp,
> > When: ( Q1A=1 AND Q1E=1 AND Q1F=1 AND Q1G IN
> > (,NA) ) THEN SET (Q1E=6,Q1F=5, Q1G=999),
> > OR When ( (Q2A=1 AND Q2E=1 AND Q2F=1 AND Q2G IN
> > (,NA) THEN SET (Q2E=6,Q2F=5,Q2G=999))
> >
> > The record falls in one of above cases should be
> > updated.
> > The query I used is:
> > UPDATE temp
> > SET Q1E = IF(Q1E = 1, 6, Q1E),
> > Q1F = IF(Q1F = 1, 5, Q1F),
> > Q1G = IF(Q1G IN ('','NA'),999, Q1G),
> > Q2E = IF(Q2E = 1, 6, Q2E),
> > Q2F = IF(Q2F = 1, 5, Q2F),
> > Q2G = IF(Q2G IN ('', 'NA'), 999, Q2G),
> >WHERE Q1A = 1 AND Q1E = 1 AND Q1F = 1 AND (Q1G
> IN
> > ('', 'NA'))
> > OR ( Q2A = 1 AND Q2E = 1 AND Q2F = 1 AND
> Q2G
> > IN ('', 'NA'));
> >
> > This query has problem. It also updates the record
> > like following:
> > Before update: Q1A=1,Q1E=1,Q1F=1,Q1G=NA, Q2A=1,
> > Q2E=3, Q3F=1;
> > After update: Q1A=1,Q1E=6,Q1F=5,Q1G=999, Q2A=1,
> Q2E=3,
> > Q3F=5.
> > However, since Q2E=3, this record should be:
> > Q1A=1,Q1E=6,Q1F=5,Q1G=999, Q2A=1, Q2E=3, Q3F=1.
> >
> > So, any suggestion?
> >
> > Thanks a lot.
>
>
___
Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now.
http://promotions.yahoo.com/goldrush
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about Update multiple columns at once
Im trying to update multiple columns at once but
cannt do it in an efficient way.
What I am trying to do is:
Update table temp,
When: ( Q1A=1 AND Q1E=1 AND Q1F=1 AND Q1G IN
(,NA) ) THEN SET (Q1E=6,Q1F=5, Q1G=999),
OR When ( (Q2A=1 AND Q2E=1 AND Q2F=1 AND Q2G IN
(,NA) THEN SET (Q2E=6,Q2F=5,Q2G=999))
The record falls in one of above cases should be
updated.
The query I used is:
UPDATE temp
SET Q1E = IF(Q1E = 1, 6, Q1E),
Q1F = IF(Q1F = 1, 5, Q1F),
Q1G = IF(Q1G IN ('','NA'),999, Q1G),
Q2E = IF(Q2E = 1, 6, Q2E),
Q2F = IF(Q2F = 1, 5, Q2F),
Q2G = IF(Q2G IN ('', 'NA'), 999, Q2G),
WHERE Q1A = 1 AND Q1E = 1 AND Q1F = 1 AND (Q1G IN
('', 'NA'))
OR ( Q2A = 1 AND Q2E = 1 AND Q2F = 1 AND Q2G
IN ('', 'NA'));
This query has problem. It also updates the record
like following:
Before update: Q1A=1,Q1E=1,Q1F=1,Q1G=NA, Q2A=1,
Q2E=3, Q3F=1;
After update: Q1A=1,Q1E=6,Q1F=5,Q1G=999, Q2A=1, Q2E=3,
Q3F=5.
However, since Q2E=3, this record should be:
Q1A=1,Q1E=6,Q1F=5,Q1G=999, Q2A=1, Q2E=3, Q3F=1.
So, any suggestion?
Thanks a lot.
__
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
