CASE WHEN THEN error

[SimonIoa]

Hello i am getting an error on a CASE WHEN THEN syntax on my mySql

         SELECT  DISTINCT u.uid,c.c_id,u.name,u.profile_pic,u.username,u.email,c.time
                FROM conversation c, users u, conversation_reply r
                CASE
                WHEN c.user_one = '99'
                THEN c.user_two = u.uid
                WHEN c.user_two = '99'
                THEN c.user_one= u.uid
                END
                AND (
                c.user_one ='99'
                OR c.user_two ='99'
                ) AND u.status='1' AND c.c_id=r.c_id_fk AND u.uid<>'1'ORDER BY c.time DESC LIMIT 15

 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASE
            WHEN c.user_one = '99'
            THEN c.user_two = u.uid
            WHEN c.user_t' at line 3

[rproffitt]

I'd consider rewriting this. Use the code format you see at https://www.w3schools.com/sql/func_mysql_case.asp

[ryantroop]

Your where clause always have to result in a boolean to check equivalency. For example "where 1 = 1". From reading this, it does not look like the case has a match, and therefore it is in error.

Ex: 1 = case u.foo when t.foo then 1 else 0 end and ....

[Reverend Jim]

As I recall from my old SQL days, the syntax is not

WHEN fld='value'
THEN var='other value'

it's

SELECT fld1, fld2,
    CASE
        WHEN fld2='val1' THEN 'new val1'
        WHEN fld2='val2' THEN 'new val2'

This maps database values for fld2 onto user specified values. Sorry I can't actually try this out. I haven't had SQL installed for some years.

[ryantroop]

You're also missing the word "where" before your case. Lastly, comma joins are pretty old and likely will be deprecated at some point...

[Sherin Mathew]

Replace CASE block with this!

Update [TableName] 
set c.user_one=case when c.user_two=99  then u.uid else c.user_one end , c.user_two=case when c.user_one=99  then u.uid    else c.user_two end

[Viki_1]

Two posts above the correct solution.