What is not valid about this MySQL syntax for CASE WHEN?

Question

I'm trying to use the CASE WHEN statement, but it doesn't seem to work. Is there any change that I can make to this in order to make it work, or is it completely wrong?

"CASE

    WHEN (a != '1' AND b != '1' AND c != '1' AND d != '1' AND e != '1') WHERE id = :id
        THEN UPDATE table SET f = 0 WHERE f = 1

    WHEN (a != '1' AND b != '1' AND c != '1' AND d != '1' AND e != '1') WHERE id = :id
        THEN UPDATE table SET f = 1 WHERE id = :id

    ELSE f

END"

I really can't figure this out, and I have tried looking for an answer in different places, but I can't find something similar. Thanks.

Edit: This is not part of a SELECT statement. I have six columns and one unique id for each row. What I want, is that each row only have one of its columns set to 1, at a time. If a row has already one column set to 1, then you can't set another of its columns to 1. Also, if one column is already set to one in one row, then the same column can't be set to 1 in another row. So I guess that I want it to be mutually exclusive, both vertically and horizontally, hence why both WHEN statements are the same, the first one so that all previous rows that had a 1 in that column will be reseted to 0, before assigning the 1 to the specific chosen column (by id). I placed a WHERE inside the CASE statement because I thought you could do that, judging by the comments, I guess not.

That's the full query, I am using prepared statements, and before that, I had this:

$q = $con->query("UPDATE table SET f=0 WHERE f=1");
$q = $con->prepare("UPDATE table SET f=1 WHERE id = :id");
$q->execute(array('id' => "$id"));

I'm really a beginner with MySQL, so maybe my solution is laughable, and I'm sorry for that. I hope I could explain clearly what I want to do. I don't know where to find the error log, but after posting this, I'm looking for it, and will add a further update. So far I can tell you that I don't know if there's an error (but I'm pretty sure there is, judging by the replies), but what I can tell you is that upon executing the query, it does nothing.

Edit2: Here's the error that I get when using the query that I posted:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE WHEN ...'

I'm trying other solutions...

Edit3: Hello, I've managed to find a way to do what I was trying to do. I couldn't do it using only the CASE THEN syntax, and in fact I had to write a whole lot of new code (and queries), so I'm guessing that what I did is NOT the simplest way (and definitely not that fast), and I'm pretty sure it's doable with just one query, if so, the answer is appreciated, and I'm thankful with all of you who tried to help! Apparently I didn't explain myself well. I tried the code you all suggested, and it all works (there's one exception with someone's code, which I'll clarify), but it doesn't serve my purpose. Anyway, here's what I did:

$q = $con->prepare("SELECT id FROM table WHERE f = 1"); //Find the id of the row that has already f = 1
$q->bindColumn(1, $prev_id);           //store as $prev_id, it might be useful later
$q->execute();
$q->fetch();

$q = $con->query("UPDATE table SET f = 0 WHERE f = 1"); //Reset row to f = 0

$q = $con->prepare("
UPDATE table
SET f = CASE WHEN (a != '1' AND b != '1' AND c != '1' AND d != '1' AND e != '1') 
THEN '1' 
ELSE f                                              //The original query, IF
END                                                //selected row != 1 on every other 
WHERE id = :id                                    //column, then f = 1 on selected id
");                                               //else, keep previous state (f = 0)
$q->execute(array('id' => "$id"));

$q = $con->prepare("SELECT f FROM table WHERE id = :id");
$q->bindColumn(1, $last_value);
$q->execute(array('id' => "$id"));       //Get value of f on last modified id (from previous
$q->fetch();                            //query) and store it

       if($last_value == 0)          //if f = 0, it means the selected id was not modified.
        {                           //Then, restore f = 1 on the row that previously had it

            $q = $con->prepare("UPDATE table SET f = 1 WHERE id = :prev_id");
            $q->execute(array('prev_id' => $prev_id));

        }

Anyway, thanks to you all, I've learnt a lot today, and it's been fun. Still, I guess this is NOT the best way to do this, and if anyone comes up with a better answer, I'll appreciate it!

Please don't write `it doesn't work`. Explain whether it is showing error or not displaying output. If showing error means post the output. Also post full query for us to analyze. — krishna, Nov 20 '14 at 05:25
Sorry, I tried to be as brief as possible, since last time I made a huge post. Will update post with details (hopefully brief and briefly). — JackTheStack, Nov 20 '14 at 05:30
What is the difference between the two conditional expressions in the two `WHEN` statement. Aren't they same? — Suvendu Shekhar Giri, Nov 20 '14 at 05:35
to throw errors in pdo statement check this question http://stackoverflow.com/questions/15275689/error-checking-for-pdo-prepared-statements . Once you add this you can find whats the error it is having — krishna, Nov 20 '14 at 05:51
Thanks, I've managed to do so. I have also updated the post. — JackTheStack, Nov 20 '14 at 07:14

score 1 · Answer 1 · answered Nov 20 '14 at 05:25

1

please check below:

UPDATE  table
SET f =
    CASE
        WHEN (a != '1' AND b != '1' AND c != '1' AND d != '1' AND e != '1') THEN
            0
        WHEN (a != '1' AND b != '1' AND c != '1' AND d != '1' AND e != '1') THEN
            1
        ELSE f
    END
WHERE ID = :id

But here both the WHEN clause condition are same.

answered Nov 20 '14 at 05:25

prava

3,916
2
24
35

As you say the both condition are same why do you write two when clause. check the second update , the condition set was different. It will overwrite `f` value as 1. – krishna Nov 20 '14 at 05:34
@krishna, I don't see any wrong in there. Yes, both the CASE condition are same and answer mentions it clearly which should be more enough. It's now up to OP to clarify it further. – Rahul Nov 20 '14 at 05:39
I dint mention anything wrong here. What i was trying to say is it will overwrite f value as 1, then no need of first when. – krishna Nov 20 '14 at 05:45
Is there any way to make the first WHEN apply to ALL other fields that have f set as one already in order to make them set it as zero? (I'm saying them, but there should be only one field with f set as one). On the first WHEN I want to refer to the whole table, not just the row with the chosen id. Thanks. – JackTheStack Nov 20 '14 at 07:20

redyaffle · Answer 2 · 2014-11-21T13:29:39.897

1

It seems that you don't need an update when a, b, c, d, and e are not equal to 1. In which case, you can use a where clause on your update (and remove the WHERE from your CASE statement):

UPDATE table
SET f = 
        CASE 
           WHEN f = 1 THEN 0
           ELSE 1
        END
WHERE a != '1' AND b != '1' AND c != '1' AND d != '1' AND e != '1'
      AND ID = :id;

There's no sense updating f to equal f, as there's no change.

Also, typically a CASE statement follows this format: CASE WHEN condition THEN result WHEN condition THEN result ELSE default_value END

See http://dev.mysql.com/doc/refman/5.0/en/case.html (MySQL) and http://www.postgresql.org/docs/9.2/static/functions-conditional.html (PostgreSQL).

edited Nov 21 '14 at 13:29

answered Nov 20 '14 at 05:36

redyaffle

171
1
5

1

Your `WHERE` can be simplified to `WHERE 1 NOT IN (a,b,c,d,e)` – Rahul Nov 20 '14 at 05:37
I'm not familiar with that syntax. Do you have a documentation link for that? – redyaffle Nov 20 '14 at 05:39
I think AND is missing before ID... isn't it? If I don't add it, an error shows. Thanks – JackTheStack Nov 20 '14 at 09:01

Russell Jonakin · Answer 3 · 2014-11-20T05:58:06.220

1

this should work:

UPDATE  table
SET f =
    CASE
        WHEN (a != '1' AND b != '1' AND c != '1' AND d != '1' AND e != '1' AND f = '1') THEN
            0
        WHEN (a != '1' AND b != '1' AND c != '1' AND d != '1' AND e != '1') THEN
            1
        ELSE f
    END
WHERE ID = :id

The first When clause takes in account the OP's need to set f to 0 when f = 1.

edited Nov 20 '14 at 05:58

answered Nov 20 '14 at 05:43

Russell Jonakin

1,716
17
18

What is not valid about this MySQL syntax for CASE WHEN?

3 Answers3