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.
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.
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
Hi.
Sorry I meant UPDATE a column within the row depending on the data of another column.
Is this what you need?
insert into table1
set col2='whatever'
where col1 = 1234
UPDATE table1
SET col2=‘whatever’
WHERE col1 = 1234
Hi.
I would like to achieve something like this:
UPDATE table1
if col1 = 0
SET col2=‘hello’
elseif col1 = 1
set col2 = ‘bye’
UPDATE table1
SET col2 =
CASE
WHEN col1 = 0 THEN 'hello'
WHEN col1 = 1 THEN 'bye'
ELSE col2
END
Hi.
How would I do this on DUPLICATE KEY UPDATE?
add a WHERE condition to the update with the key value.
hi it does not seem to work.
ON DUPLICATE KEY WHERE up = 1 UPDATE up = 0 AND down = 0
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
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
“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”
Hi
$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
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
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.
Hi. It’s working now. Thanks for your help!!!
down =
CASE
WHEN up = 1
THEN 0
WHEN up = 0
THEN 1
END
,
up = 0
Blimey UPDATE is what I meant. Must ensure I am more awake when posting.