SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    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
    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?

  2. #2
    SitePoint Wizard
    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

  3. #3
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    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!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    UPDATE mytable
       SET display = MOD(display+1,2)
     WHERE whatever
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    UPDATE mytable
       SET display = ABS(display-1)
     WHERE whatever
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard
    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

  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    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.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    CASE is standard SQL, MOD isn't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    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.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by swampBoogie View Post
    MOD is standard SQL too.
    not according to Celko's SQL for Smarties 3rd edition

    what is it with you knowing so much more about standards than Celko?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    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

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    LOLZ, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •