Correct SQL Syntax

Discussion in 'Plugin Development' started by codename_B, Sep 5, 2012.

Thread Status:
Not open for further replies.
  1. Offline

    codename_B

    Is this correct SQL syntax?

    Code:
    UPDATE game_data
        SET kills = CASE name
            WHEN 'FlyByMoon' THEN 3
            WHEN 'codename_B' THEN 52
            WHEN 'LordOfLaughter' THEN 12
            WHEN 'DeathByMyHand' THEN 145
        END
    WHERE id IN ('FlyByMoon', 'codename_B', 'LordOfLaughter', 'DeathByMyHand')
    
    And is this correct SQL synax?

    Code:
    UPDATE game_data SET kills = CASE name WHEN 'FlyByMoon' THEN 3 WHEN 'codename_B' THEN 52 WHEN 'LordOfLaughter' THEN 12 WHEN 'DeathByMyHand' THEN 145 END WHERE id IN ('FlyByMoon', 'codename_B', 'LordOfLaughter', 'DeathByMyHand')
    
     
  2. Offline

    Lolmewn

    I never use WHEN and stuff.
    Is SQL different from MySQL? Because I always use that :p
     
  3. Offline

    codename_B

    Erm? XD

    Is it correct MySQL syntax?
     
  4. Offline

    Giant

    It seems to be correct syntax. Try it out and see if it burps any errors? :)

    Lolmewn, MySQL has some VERY great differences from normal SQL syntax...
     
  5. Offline

    Lolmewn

    Well I better never jump into SQL then :p
     
  6. Offline

    codename_B

    I don't have a database handy to test it on, lol!

    I'll put one together later...
     
  7. Offline

    Giant

    Or just use normal SQL syntax and not MySQL syntax, as in that case you won't run into any issues unless you start toying with SQLite! :D

    Hehe, fair enough...
     
  8. Offline

    codename_B

    Is that the best way to update 150 odd results in one query?
     
  9. Offline

    Giant

    codename_B Hmm, it would depend on how the update would be... If you simply want to increase the "kills" column by 1, I would suggest doing simply
    Code:
    UPDATE game_data
    SET kills = kills + 1
    WHERE id IN ('FlyByMoon', 'codename_B', 'LordOfLaughter', 'DeathByMyHand')
    
    This way would sort of safe you from having to select the previous data as well... However, if you had your data cached, and are now simply syncing it back to the server, it is probably going to be better to use your query! :)
     
  10. Offline

    codename_B

    One more thing... Since the column is "name" rather than "id"

    Code:
    WHERE name IN etc?
    
     
  11. Offline

    Giant

    That would probably make more sense yes.
     
  12. Offline

    codename_B

    Yay :D

    I think thats my mega-update query thread condensed from MANY QUERIES to one.
     
Thread Status:
Not open for further replies.

Share This Page