SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    844
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Is this query in the right format?

    I pieced this query together using some stuff I found on the web. I'm afraid to run it because once an update is done it can't be undone. Does this look good to you?

    Thanks!

    UPDATE users u, (SELECT uID AS selected_uID FROM users) AS u2 SET u.guide = u2.selected_uID
    WHERE
    u.member = 'No' and
    u.guide !=110 and
    u.guide!=0 and
    u.guide not in (select uID from users);
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    no, it is not right

    first, you are cross-joining u and u2, because you've forgotten a join condition

    but more importantly...
    Currently, you cannot update a table and select from the same table in a subquery.
    -- MySQL :: MySQL 5.0 Reference Manual :: 12.2.10 UPDATE Syntax
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    844
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, so what is the best way to update a whole lot of people based on multiple criteria?

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by busboy View Post
    Ok, so what is the best way to update a whole lot of people based on multiple criteria?
    i guess that would depend on the criteria, wouldn't it

    i'm not trying to be flippant, but you haven't explained what those criteria are

    it's pretty hard to discern what the relationship of the guide and uid columns are (or should be)
    r937.com | rudy.ca | 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
  •