SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    IF statement within query?

    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    Is it possible to add an IF statement within a query?
    yes, although it is better to use a CASE expression

    Quote Originally Posted by Jaynesh View Post
    I want to INSERT a certain value into the row depending on the value of another column.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi.

    Sorry I meant UPDATE a column within the row depending on the data of another column.

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is this what you need?

    Code MySQL:
    insert into table1
    set col2='whatever'
    where col1 = 1234

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

  6. #6
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi.

    I would like to achieve something like this:

    UPDATE table1
    if col1 = 0
    SET col2='hello'
    elseif col1 = 1
    set col2 = 'bye'

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    UPDATE table1
    SET col2 =
       CASE 
          WHEN col1 = 0 THEN 'hello'
          WHEN col1 = 1 THEN 'bye'
          ELSE col2
       END

  8. #8
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi.

    How would I do this on DUPLICATE KEY UPDATE?

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    add a WHERE condition to the update with the key value.

  10. #10
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi it does not seem to work.

    ON DUPLICATE KEY WHERE up = 1 UPDATE up = 0 AND down = 0

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    hi it does not seem to work.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    ... but when I add an extra WHEN, it stops working.
    "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"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    $stops working = it just doesn't update the column.

    Code:
    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

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  17. #17
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi. It's working now. Thanks for your help!!!

    down =
    CASE
    WHEN up = 1
    THEN 0
    WHEN up = 0
    THEN 1
    END

    ,

    up = 0

  18. #18
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    UPDATE table1
    SET col2='whatever'
    WHERE col1 = 1234
    Blimey UPDATE is what I meant. Must ensure I am more awake when posting.


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
  •