SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Query Error

  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2005
    Posts
    436
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query Error

    I'm receiving a query error and im not sure why. It is supposed to duplicate a specific set of rows in a table. Which rows is determined by a different table. Is therea clear syntax error?

    Code MySQL:
    INSERT INTO listings VALUES (SELECT * FROM listings WHERE id = (SELECT list_id FROM groupings WHERE group_id IN(2) ) )

    The MySQL error said it was
    near ''(SELECT * FROM listings WHERE id = '(SELECT list_id FROM g'
    e39m5

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i'm having a bit of trouble understanding your sql

    could you explain in words what you are trying to do with that query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by e39m5 View Post
    I'm receiving a query error and im not sure why. It is supposed to duplicate a specific set of rows in a table. Which rows is determined by a different table. Is therea clear syntax error?

    Code MySQL:
    INSERT INTO listings VALUES (SELECT * FROM listings WHERE id = (SELECT list_id FROM groupings WHERE group_id IN(2) ) )

    The MySQL error said it was

    e39m5
    You do not use the "VALUES" keyword in an INSERT INTO...SELECT query.

    Code MySQL:
    INSERT INTO listings SELECT * FROM listings WHERE id IN (SELECT list_id FROM groupings WHERE group_id = 2)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    dan, you stepped in it

    the subquery will return a bunch of list_ids, correct? but from a different table, groupings, and for only a specific group

    these list_ids are then compared to the ids in the listings table, using the IN (subquery) structure (nice silent correction of the original, by the way )

    so if there is a row in the listings table with an id which matches one of the list_ids in the selected groupings group, then the SELECT will pick it up ...

    ... and try to INSERT it into the listings table ...

    ... which at that point should generate a PK error, because it's already in the listings table!

    ... which is why i wanted clarification, eh
    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
  •