#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    5
    Rep Power
    0

    DB table not being updated


    Hello,

    I have a database that contains a listing of books. All the books are assigned to various categories. The products table has a column called category which contains the category that book belongs to. One of the categories called "Openings" is too large so I'm going to break up this into multiple categories. Product codes 93 - 131 are titles that start with a number and run through the letter B and will be changed from Openings to Openings-1-b. Books with product codes 132-164 start with C - E, hence the category in the table for those books will be changed from Openings to Openings-c-e. If this works, then I'll modify the code to change the category of the other books.

    Having over 318 items in this category I decided to try phpmyadmin sql to do the replacements instead of individually. Here is my initial code for the first group:

    UPDATE kgnvt_sc_products SET category = replace(category, 'Openings', 'Openings-1-b') WHERE prodcode >= '93' AND prodcode <= '131'

    However, when I hit go it says that 0 records have been updated. Can you help me with the proper syntax?

    Thank you.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,442
    Rep Power
    1688
    What happens when you use:

    SQL Code:
    SELECT prodcode 
         , category
      FROM kgnvt_sc_products
         WHERE prodcode >= '93' 
           AND prodcode <= '131'
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    5
    Rep Power
    0
    Thanks for your reply. Here's what happened:

    MySQL returned an empty result set (i.e. zero rows). (Query took 0.0238 sec)
    I checked one of the products to make sure that I didn't inadvertently have a space before or after the number.

    Originally I imported the data with a csv file. The prodcode type is varchar(64) if you think that makes a difference.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    Originally Posted by chriso0258
    The prodcode type is varchar(64) if you think that makes a difference.
    it sure as heck does

    it's impossible for a last name to be greater than or equal to 'ZORRO' and at the same time less than or equal to 'BLACK'

    see where i'm going with this?

    VARCHAR comparisons are character-by-character, left to right
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    5
    Rep Power
    0
    Thank you. I changed the type to INT (11) and tried SimonJM's code and got the selected results.

    Many thanks
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,442
    Rep Power
    1688
    I have a rule of thumb that if I am going to do an update to data (UPDATE or DELETE) I do a SELECT with the same WHERE/HAVING clauses to convince myself I am going to hit the right rows.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc

IMN logo majestic logo threadwatch logo seochat tools logo