SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2008
    Posts
    551
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Updating a table column with numeric value sql

    Hi all
    I have a mysql table called vehicle_type and it has a column called cylinder_id
    The value in cylinder_id is a number that is defined by the value in the column cylinders.

    I want to update all the values in cylinder_id to 1 where the value in cylinders is between 0-99

    This is my attempt- what am I doing wrong? Thank you

    Code:
    UPDATE CYLINDER_ID '1'
    VEHICLE_TYPE WHERE 
    CYLINDERS 0>99

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you were just guessing, weren't you
    Code:
    UPDATE vehicle_type 
       SET cylinder_id = 1
     WHERE cylinders BETWEEN 0 AND 99
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2008
    Posts
    551
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you Rudy! I am now updating cylinder_id when it is 3 when the vclass is 1 and the cylinders are 200 or more. Does this code look correct? Thank you

    UPDATE vehicle_type
    SET cylinder_id = 3
    WHERE vclass = 1 and cylinders >200

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Bayliss Trevor View Post
    Does this code look correct?
    what happened when you tested it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Mar 2008
    Posts
    551
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is the message so it appears it hasn't changed anything, the valuemust already be correct:
    [SQL]Affected rows: 0
    Time: 0.401ms

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you can double-check it by running this --
    Code:
    SELECT DISTINCT cylinder_id
      FROM vehicle_type
     WHERE vclass = 1 
       AND cylinders > 200
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Mar 2008
    Posts
    551
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I get 3 as a result, I have done a new query with but it doesn't affect any rows, I have checked that there are values in the database that are vclass 1 and have cylinders with value 200. They are not being updated and I don't understand why - maybe I have to do change the cylinders to >199 so it will update those values?

    UPDATE vehicle_type
    SET cylinder_id = 3
    WHERE vclass = 1 and cylinders >200

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Bayliss Trevor View Post
    maybe I have to do change the cylinders to >199 so it will update those values?
    yes

    alternatively, use
    Code:
    cylinders >= 200
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist
    Join Date
    Mar 2008
    Posts
    551
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks


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
  •