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

    Join Date
    Mar 2012
    Posts
    59
    Rep Power
    3
    some of this seems unnecessary, so i'm not sure i understood the question
    but how's this:
    SELECT likesd a
    INNER JOIN (select parent, country, sum(votes) as sumvotes
    from likesd
    group by parent, country) b on a.type=b.parent and a.country=b.country
    SET a.votes=b.sumvotes
    WHERE a.type=2
  3. #3
  4. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by bobert123
    but how's this:
    SET is not valid for SELECT
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  5. #4
  6. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    59
    Rep Power
    3
    sorry should say:
    UPDATE likesd a
    INNER JOIN (select parent, country, sum(votes) as sumvotes
    from likesd
    group by parent, country) b on a.type=b.parent and a.country=b.country
    SET a.votes=b.sumvotes
    WHERE a.type=2
    thanks r937 for catching that.
  7. #5
  8. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by da mysql manual
    Currently, you cannot update a table and select from the same table in a subquery.
    -- http://dev.mysql.com/doc/refman/5.7/en/update.html
    could probably use a temp table, yes?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  9. #6
  10. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    59
    Rep Power
    3
    this might be a bit off topic, but then why does this workaround work:
    http://stackoverflow.com/questions/1955988/mysql-select-inside-update

IMN logo majestic logo threadwatch logo seochat tools logo