Re: Question about case statement

[Michael Stassen]

The original problem was that CASE returns the result corresponding to the 
matching condition.  It isn't really a flow-control function.

I don't think Lachlan's suggestion is right either, though.  Its missing a 
comma, I think, but more importantly, it will set QE and QF to NULL whenever 
they aren't 1.  I don't think that's what you had in mind.  I expect you 
want QE and QF to remain unchanged unless they are 1.

Also, your WHERE condition seems a little strange to me.
  (QE != 6 OR QF != 5)
is equivalent to
  NOT(QE = 6 AND QF = 5)
so that will match every row except those where both QE and QF are already 6 
and 5.  As you are only changing QE and QF when they are 1, there's no sense 
retrieving rows where they are not 1 just to do nothing to them.

So, using CASE, I expect you need something like:
  UPDATE temp
  SET QE = CASE WHEN QE = 1 THEN 6 ELSE QE END,
      QF = CASE WHEN QF = 1 THEN 5 ELSE QF END
  WHERE QA = 1 AND (QE = 1 OR QF = 1);
or
  UPDATE temp
  SET QE = CASE QE WHEN 1 THEN 6 ELSE QE END,
      QF = CASE QF WHEN 1 THEN 5 ELSE QF END
  WHERE QA = 1 AND (QE = 1 OR QF = 1);
As you really only have one condition per column, however, CASE is overkill. 
 A simple IF would do:

  UPDATE temp
  SET QE = IF(QE = 1, 6, QE),
      QF = IF(QF = 1, 5, QF)
  WHERE QA = 1 AND (QE = 1 OR QF = 1);
Finally, if you meant that QF should be changed from 1 to 5 only when QE was 
not changed from 1 to 6, then this still isn't right.  If that's the case, I 
think you need something like:

  UPDATE temp SET QE = 6 WHERE QA = 1 AND QE = 1;
  UPDATE temp SET QF = 5 WHERE QA = 1 AND QF = 1 AND QE != 6;
Michael
Lachlan Mulcahy wrote:
Hi there,
What you probably want is
UPDATE temp
SET
        QE = CASE WHEN QE = 1 THEN 6 END
        QF = CASE WHEN QF = 1 THEN 5 END
WHERE
QA = 1 AND (QE <> 6 OR QF <> 5)
http://dev.mysql.com/doc/mysql/en/Control_flow_functions.html
Lachlan
-----Original Message-----
From: Mo Li [mailto:[EMAIL PROTECTED]
Sent: Thursday, 12 August 2004 9:01 AM
To: [EMAIL PROTECTED]
Subject: Question about case statement
Hello,
I'm trying to update table by using case statement.
I used following syntax:
UPDATE temp
SET
CASE WHEN QE = 1 THEN QE = 6 WHEN QF = 1 THEN QF = 5
END
WHERE QA = 1 AND ( QE != 6 OR QF !=5)
but it didn't work. I checked the manual and didn't
see where is the problem.
Thanks for any help.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]