I don't think your problem has anything to do with your Update statement or Select statements, assuming you reported them accurately.
Could another user of the system have emptied your table? Could you have inadvertently executed a statement or a script that would have emptied it? Those seem like the obvious explanations to me. If neither of those is the cause, you may have stumbled on a really serious bug. Rhino ----- Original Message ----- From: "Monet" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]>; "mysql" <[EMAIL PROTECTED]> Sent: Thursday, September 09, 2004 4:02 PM Subject: 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]
