IF statement within query?

Databases
1

Hi

Is it possible to add an IF statement within a query?

I want to INSERT a certain value into the row depending on the value of another column.

2

yes, although it is better to use a CASE expression

could you please be a bit more specific?

note that you cannot insert “a certain value into the row”, you can only insert an entire row into the table

3

Hi.

Sorry I meant UPDATE a column within the row depending on the data of another column.

4

Is this what you need?


insert into table1
set col2='whatever'
where col1 = 1234
5

UPDATE table1
SET col2=‘whatever’
WHERE col1 = 1234

6

Hi.

I would like to achieve something like this:

UPDATE table1
if col1 = 0
SET col2=‘hello’
elseif col1 = 1
set col2 = ‘bye’

7 

UPDATE table1
SET col2 =
   CASE 
      WHEN col1 = 0 THEN 'hello'
      WHEN col1 = 1 THEN 'bye'
      ELSE col2
   END
8

Hi.

How would I do this on DUPLICATE KEY UPDATE?

9

add a WHERE condition to the update with the key value.

10

hi it does not seem to work.

ON DUPLICATE KEY WHERE up = 1 UPDATE up = 0 AND down = 0

11

please don’t guess

look up the syntax in the manual, and you will see that you cannot put WHERE right after ON DUPLICATE KEY

12

Hi thanks.

I’ve got working but when I add an extra WHEN, it stops working.

up =
CASE
WHEN up = 1
THEN 0
END

AND

down =
CASE
WHEN up = 1
THEN 0
WHEN up = 0
THEN 1
END

13

“stops working” is not one of the mysql error messages that i’m familiar with

could you show the entire SQL statement please

then describe more about how it “stops working”

14

Hi :slight_smile:

$stops working = it just doesn’t update the column.

INSERT INTO dbKarma(karma_id, karma_user_id, karma_post_id, up, down) VALUES ('$user/$down', $user, $down, 0, 1)
ON DUPLICATE KEY UPDATE 

up = 
    CASE 
    WHEN up = 1
    THEN 0
    END
    
    AND
    
down = 
    CASE 
    WHEN up = 1
    THEN 0
    WHEN up = 0
    THEN 1
    END
15

the AND is wrong, it should be a comma

i think the reason you’re not getting a syntax error on it is because it is performing a logical and …

… UPDATE up = x AND y

here x is CASE WHEN up = 1 THEN 0 END which is either 0 or NULL

and y is down = CASE WHEN up = 1 THEN 0 WHEN up = 0 THEN 1 END which will be TRUE or FALSE ( i.e. 1 or 0) depending on what down is

so when up is 1 then x AND y is 0 (because 0 ANDed with either 0 or 1 is 0)

and when up is 0 then x AND y is NULL

make sense?

never mind if it doesn’t, just go back to the syntax in the manual and realize that you cannot use the word AND like that

16

Hi.

It is still not working as I intend it to.

Perhaps im over-complicating it. Here is what I want to achieve.

Ive got a table
vote(up, down)

Ive got two arrows. (up and down)

When somebody clicks the UP arrow.
It will update the table
(up, down)
1, 0

When somebody clicks the DOWN arrow
It will update the table
(up, down)
0, 1

What I want it to do is:
IF the vote is
(up, down)
1, 0

and I click on the down arrow, it will reset both values back to zero.
(up, down)
0, 0

When I click on the down arrow again, it will update to
(up, down)
0, 1

The CASE query I provided is part of the down vote. I intend to add it to the up vote query too.

17

Hi. It’s working now. Thanks for your help!!!

down =
CASE
WHEN up = 1
THEN 0
WHEN up = 0
THEN 1
END

,

up = 0
18

Blimey UPDATE is what I meant. Must ensure I am more awake when posting. :blush: