SitePoint Sponsor |
|
User Tag List
Results 1 to 12 of 12
-
Nov 9, 2009, 15:40 #1
- Join Date
- Oct 2006
- Location
- France, deep rural.
- Posts
- 6,869
- Mentioned
- 17 Post(s)
- Tagged
- 1 Thread(s)
Making an sql "toggle" using a single query?
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
"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?
-
Nov 9, 2009, 15:43 #2
- Join Date
- Mar 2002
- Location
- Bristol, UK
- Posts
- 2,240
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Nov 9, 2009, 16:00 #3
- Join Date
- Oct 2006
- Location
- France, deep rural.
- Posts
- 6,869
- Mentioned
- 17 Post(s)
- Tagged
- 1 Thread(s)
Yeah, thats the one, thanks a lot SJH!
-
Nov 9, 2009, 19:01 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Code:UPDATE mytable SET display = MOD(display+1,2) WHERE whatever
-
Nov 9, 2009, 19:03 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Code:UPDATE mytable SET display = ABS(display-1) WHERE whatever
-
Nov 10, 2009, 03:02 #6
- Join Date
- Mar 2002
- Location
- Bristol, UK
- Posts
- 2,240
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Nov 10, 2009, 05:15 #7
- Join Date
- Oct 2006
- Location
- France, deep rural.
- Posts
- 6,869
- Mentioned
- 17 Post(s)
- Tagged
- 1 Thread(s)
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.
-
Nov 10, 2009, 07:04 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
CASE is standard SQL, MOD isn't
-
Nov 10, 2009, 07:43 #9
- Join Date
- Jan 2004
- Location
- Uppsala, sverige
- Posts
- 700
- Mentioned
- 2 Post(s)
- Tagged
- 1 Thread(s)
MOD is standard SQL too. ABS as well.
-
Nov 10, 2009, 07:45 #10
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Nov 10, 2009, 14:04 #11
- Join Date
- Jan 2004
- Location
- Uppsala, sverige
- Posts
- 700
- Mentioned
- 2 Post(s)
- Tagged
- 1 Thread(s)
I've got the real heavy stuff.
Abs and mod was both added in SQL-99
-
Nov 10, 2009, 14:35 #12
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
LOLZ, eh
Bookmarks