----- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
