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

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 ....

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.

commented: And as the examples I read, FROM is done at the end. +0

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

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

Two posts above the correct solution.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.