Making an sql "toggle" using a single query?

**Cups** · Nov 9, 2009, 14:40

I have a simple show/hide requirement in an application.

Is it possible to change a single value using sql alone?

Code:

mytable
======
title     | VAR
display | INT // either 1 or 0

So that if the values in my table were:

"My article title"
1

Given that the query contained " ... where title = 'My article title' ... " it could automatically swop that to :

"My article title"
0

and vice versa?

It is early days so I could change the field to "Null" / 1 if that would make things easier?

**SJH** · Nov 9, 2009, 14:43

Untested, but should work:

Code sql:

UPDATE mytable SET display = CASE WHEN display = 1 THEN 0 ELSE 1 END WHERE title = 'My article title';

Btw, TINYINT might be a more suitable datatype for that column

**Cups** · Nov 9, 2009, 15:00

Yeah, thats the one, thanks a lot SJH!

**r937** · Nov 9, 2009, 18:01

Code:

UPDATE mytable
   SET display = MOD(display+1,2)
 WHERE whatever

**r937** · Nov 9, 2009, 18:03

Code:

UPDATE mytable
   SET display = ABS(display-1)
 WHERE whatever

**SJH** · Nov 10, 2009, 02:02

You know r937, I'm getting a bit fed up with giving people advice in this forum only for you to come along and give better, more concise advice

**Cups** · Nov 10, 2009, 04:15

Great, I did not know about the MOD function, which I would have used if I was doing the same in PHP.

Can I ask then, are either :

MOD or CASE THEN ELSE END

Mysql only? Or are they both *sql agnostic?

(or 'basic SQL', I am not sure of the correct term to use)

Thanks again.