Trouble comparing dates in two tables

Databases
21

Ugh. So am I screwed? How come “date_format(s.date, ‘%Y-%m-%d’) as searchDate” doesn’t work?

22

why do you say that?

the query is working correctly, and it’s telling you when there are searches rows with a later datetime than the user’s last login

isn’t that what you wanted?

23

Ok, I guess you’re right. Now we just need to update the 3,000+ rows in the users table. You have a trick up your sleeve so that we can take a datetime from the searches table and update the users table with just a date? It’s amazing how complex these databases can be.

24

first thing i think you need to do is change the last column in the users table from date to datetime

you mentioned that you fixed the problem in the application that was not updating this correctly, right?

can you change it to update a datetime instead?

25

Ok, I did a little bit of testing and determined that changing the field to a datetime has no affect on users logging in, nothing crashes. Fire when ready with the update portion.

Thanks.

26

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 :slight_smile:

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…

:slight_smile:

27

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;

28

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.

29

yes, but it was a decent try :slight_smile:

however, you didn’t use the same join, as i suggested

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

30

This line should really be the following, right?

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.

31

yeah, sorry about the typo, it’s author, not author_id

and yes, testing it on a single author is a good idea