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]
