update mysql database according to the current values in it

Question

How do I update the database according to the current values in it. I currently have the following -

mysqli_query($con,
"UPDATE Tasks
SET completed = CASE WHEN completed == 0 THEN 1 ELSE 0 END
WHERE inde=$q");

The above doesn't work but when I try to do

mysqli_query($con,
"UPDATE Tasks
SET completed = 1
WHERE inde=$q");

it changes the value when ever it is 0 which is correct but I also want to change it to 0 when it is 1. Can someone tell me what I am doing wrong

score 8 · Accepted Answer · edited May 23 '17 at 12:24

8

that should be:

UPDATE  Tasks set completed= IF(completed=1, 0, 1) WHERE inde=$q LIMIT 1

limit 1, if inde is a unique index, speeds up the update (minuscule for small tables, may be worth while for large ones)

... Limit 1 probably does nothing and may be a bad idea. Is there any point using MySQL "LIMIT 1" when querying on indexed/unique field?

edited May 23 '17 at 12:24

Community

1
1

answered Jun 10 '13 at 00:08

Curious where you get `LIMIT 1` from ;-) – zerkms Jun 10 '13 at 00:10
"if inde is a unique index, speeds up the update" --- do you think so? I've always thought mysql's optimizer is smart enough to see that the index is unique thus stopping searching after a row is found, hmhm – zerkms Jun 10 '13 at 00:14
1

you could be right, its a habit i picked up, research suggest, it does nothing, i'll try break this habit, after the drinking\smoking\womanising ones. – Jun 10 '13 at 00:19

score 1 · Answer 2 · answered Jun 10 '13 at 00:09

1

CASE WHEN completed == 0 THEN 1 ELSE 0 END

This should be:

CASE WHEN completed = 0 THEN 1 ELSE 0 END

or:

CASE completed WHEN 0 THEN 1 ELSE 0 END

answered Jun 10 '13 at 00:09

PleaseStand

31,641
6
68
95

score 0 · Answer 3 · answered Jun 10 '13 at 10:16

Even though this question is already answered, however, I just wanted to propose the quicked and shortest answer provided that completed only holds 0s and 1s.

UPDATE Tasks set completed=!completed WHERE inde=$q

Note the NOT(!) operator which will toggle the value itself i.e. change 1s to 0s and 0s to 1s.

update mysql database according to the current values in it

3 Answers3