SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast ganesch's Avatar
    Join Date
    Feb 2004
    Location
    Zürich, Switzerland
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    UPDATE with complicated WHERE condition

    I spend a couple of hours to figure this out, but my SQL is quite poor

    I have to update about 300 products in my products table. Those products belong to certain categories which are defined in another table called products_to_categories.

    My query should look something like this:

    UPDATE products SET products_tax_class_id = 0 WHERE products_id = x

    ... where x are all products belonging to a certain category.

    Is there something like an UPDATE ... SELECT command in MySQL or can I put a JOIN in my WHERE condition in order to filter only those products belonging to a certain category?

    Any help would be really appreciated
    Nick

  2. #2
    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)
    yes you can do this, it's the "multi-table" delete syntax, and it's in the fine manual, along with the remark that it only works in 4.0.something (see the manual for the something)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast ganesch's Avatar
    Join Date
    Feb 2004
    Location
    Zürich, Switzerland
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Multiple-table syntax:

    DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*] ...]
    FROM table_references
    [WHERE where_definition]
    ... but I wanted to UPDATE not DELETE

  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)
    did i say delete?

    oh, i am so sorry

    i meant to say that the multi-table update syntax is in the fine manual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast ganesch's Avatar
    Join Date
    Feb 2004
    Location
    Zürich, Switzerland
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Database: MySQL 3.23.36

  6. #6
    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)
    heh, good answer

    you'll need two queries

    the first one finds the product_ids by category
    Code:
    select products_id 
      from products_to_categories
     where categories_id = number
    then you construct a comma-delimited list of the product_ids

    (in php i think you implode them or something, in coldfusion it's the ValueList function)

    then run your upodate:
    Code:
    update products 
       set products_tax_class_id = 0 
     where products_id IN ( list of ids ) 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast ganesch's Avatar
    Join Date
    Feb 2004
    Location
    Zürich, Switzerland
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wonderful

    Thank you so much


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
  •