SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 31 of 31
  1. #26
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    that was quick, you are a lot more confident that a schema change will not upset an existing app than i am, or else you are a very fast tester

    when the user logs in now, does it record a datetime in the "last" column?

    then all we have to do is turn the SELECT statement into an UPDATE statement, which will be easy, because it uses the same join...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  2. #27
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I did some more digging around and here is what I think the query should look like. Do you see any potential problems?

    update users inner join searches on (s.author = u.uID)
    set last = (SELECT author, MAX(date) AS latest FROM searches GROUP BY author) where s.latest > u.last;
    Convert your dollars into silver coins. www.convert2silver.com

  3. #28
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes, I verified that logging in does update the last field. It only puts in 2011-05-13 00:00:00 but that's ok. Nothing crashes.
    Convert your dollars into silver coins. www.convert2silver.com

  4. #29
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    Do you see any potential problems?
    yes, but it was a decent try

    however, you didn't use the same join, as i suggested
    Code:
    UPDATE users 
    INNER 
      JOIN ( SELECT author
                  , MAX(date) AS latest 
               FROM searches 
             GROUP 
                 BY author ) AS s
        ON s.author_id = users.uID
       AND s.latest > users.last 
       SET users.last = s.latest
    but before you test it, you need to fix your app so that it actually records the current timestamp rather than midnight of the current date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #30
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    ON s.author_id = users.uID
    This line should really be the following, right?

    Quote Originally Posted by r937 View Post
    ON s.author = users.uID
    I wonder if I should somehow add another line that limits this update to a specific s.author so I can test it out first? The last time I did an update it ended up changing 8,000 rows. Ugh! LOL

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

  6. #31
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yeah, sorry about the typo, it's author, not author_id

    and yes, testing it on a single author is a good idea
    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
  •