SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Taiwan
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySql UPDATE subquery?? or...

    Hey all,

    Having some problems trying to execute a code as the following..

    UPDATE products, products_description
    SET products_description.products_description_ebay =
    (SELECT products_description_ebay
    FROM products_description
    WHERE products_id =570)
    WHERE products.products_model LIKE '%12345%'

    That query generates an error and i'm not quite sure. Basically what i'm trying to do is load a rather large HTML product description into certain products where their model numbers contain a certain string.

    Any help would be appreciated.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    and the error is...

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if i understand correctly, your SET statement should be trying to update a column of the products table, not the products_description table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Taiwan
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    and the error is...
    Check my syntax near (SELECT products_description_ebay
    FROM products_description
    WHERE products_id =570)

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Taiwan
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    if i understand correctly, your SET statement should be trying to update a column of the products table, not the products_description table?
    I'm trying to update a product description from the products_description table, but i need to cross reference the model number held in the products table

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    still doesn't make sense, especially "i need to cross-reference the model number"

    which table are you updating?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you are not running mysql 4.1 or higher. therefore you can not use subqueries. please try this query to confirm: select version();

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i just noticed what rudy is getting at. you have no relationship defined between the two tables. so even if you were using mysql 4.1 or higher, i think you would end up updating every record in your table.

  9. #9
    Scary's On The Wall
    Join Date
    Apr 2003
    Location
    PA
    Posts
    518
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just a note, subqueries do appear to function in 4.0.13 (which is less than 4.1) in UPDATE statements.

  10. #10
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Haleden
    Just a note, subqueries do appear to function in 4.0.13 (which is less than 4.1) in UPDATE statements.
    i've never seen this. can you provide an example?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    longneck, you're looking at an example right in this thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    longneck, you're looking at an example right in this thread
    a non-working example:
    Quote Originally Posted by Transition
    Check my syntax near (SELECT products_description_ebay
    FROM products_description
    WHERE products_id =570)
    that's the error you get when you're using a verion of mysql that doesn't support subqueries. am i missing something?

  13. #13
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Taiwan
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's the exact message i'm receiving..

    #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT products_description_ebay
    FROM products_description
    WH

  14. #14
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Taiwan
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    you are not running mysql 4.1 or higher. therefore you can not use subqueries. please try this query to confirm: select version();
    MySQL 4.0.25-standard

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you know, i think the problem is that you can't use subqueries before version 4.1

    okay, now that that's out of the way ...

    could you please take a moment and examine the relationship between products and products_description, and let us know, because there is a way you can do a "joined update", but we don't really know what you're trying to update from where
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Taiwan
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    you know, i think the problem is that you can't use subqueries before version 4.1

    okay, now that that's out of the way ...

    could you please take a moment and examine the relationship between products and products_description, and let us know, because there is a way you can do a "joined update", but we don't really know what you're trying to update from where
    The relationship between the two tables is a unique field called the products_id field. What i'm trying to do is update a field in products_description where a field in the products table contains a certain model number. So.... If products.products_model LIKE '%12345%' then use that unique product id associated w/ that row to set a field in products_description to the same value as products_description.products_id = 123.

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, i still don't get it

    if products.products_model LIKE '%12345%' does not guarantee a unique product, it will probably select several products

    okay, let's take each of these product ids, and do what? find the matching products_description row? does each product id correspond to exactly one and only one products_description row?

    okay, you want to take the 12345 product ids (plural) and set some field -- which field? -- in the products_description row -- which row? -- to the same value as some field -- which field? -- of the products_description row with product_id 123?

    i am so lost

    perhaps you could show us some example rows in each table, before and after the update?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •