Got a simple php page pushing some data into a MySQL table.
The table is an activity log, and I’d like to keep just 30 days of activity for each user.
I figure I can create my INSERT SQL so it UPDATES any record it finds over 30 days old with the new data, and then the # of records will be more manageable.
Any tricky ways of creating an SQL query that does this:
If there’s no records for the user, add a new one
If there’s existing records for that user, see if any are >30 days.
If yes, replace the oldest one with the new record
If no, add a new record
Let’s say the records have the fields: userid date
To make this workable, you’ll need a unique record id in your table
Then It should be:
Update table set date = Now where Id In (select top 1 id from table where userid = user and date < now - 30 order by date)
If @rowcount = 0 then //or whatever that is in MySQL
begin
Insert INTO TABLE values(userid,date) select ‘BRIAN’, NOW;
end;
If there’s no records for the user, add a new one - so nothing gets deleted there. If there’s existing records for that user, see if any are >30 days - if there are, they all get removed. If yes, replace the oldest one with the new record
-by now, no 30 days records exist, so adding a new one ‘replaces’ all that were 30 days , or adds a new record where there was not one before.
What Theo meant, and what it seems the OP wanted, is to only replace the oldest record if it is over 30 days old, not delete all records over 30 days old.
Thank you, Theo. I do not want to delete old records >30 days, just replace the oldest record that’s > 30 days with my new data. Do I really need the id?
What about something like this?
UPDATE MyTable
SET
date = Now()
WHERE
date = (SELECT date
FROM MyTable
WHERE DATEDIFF(date, Now()) > 30 AND userid="$userid"
ORDER BY date DESC
LIMIT 1);
What about if the Query returns no rows, will update automatically insert?
insert on duplicate key https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
lets you take an alternative action if the key is a duplicate
with a well selected primary key you can do this using that (which lets you try the insert and if that fails do an update instead)
If the Id is needed depends on what is unique in this case.
When I understand your scenario, the two fields together are making a record unique; you can have more than 1 record wit a specific user and you can have more than 1 record with a specific date, but you can only have 1 record with a specific combination of user and date.
If this is the case, you make it yourself a lot easier to a a unique ID field to the table, that makes it possible to uniquely identify each record on that ID.
The statement:
No, the update statement only updates the oldest record that is more than 30 days old.
That is where the if check comes in; see if a record was updated. If not, then the insert is fired.
Thank you, @anon1607461 for that explanation. I see what you mean by the IF command. I’ll have to see what the syntax is for MySQL.
You suggest that an index might “make things easier”? Since I don’t need to pull specific records, I just want to see a user’s activity for the past 30 days, I’m not sure how I would use an index or how it would help? Would it improve the speed of the WHERE function in my query?
Not quite sure how to return the count of rows returned. MySQL has a Count() function that when wrapping a query returns a column with the count, but I need to return the date.
replace would work as well
but an index would be useful since it would speed things up finding relevant rows
this is where an index would help speed this lookup
you dont specifically have to DO anything to use an index
just make sure you index the right columns and use those columns in your query should be sufficient
yes non-unique indexes can be useful as sometimes the query optimizer can just read the data from the index and never actually have to go to disk to get the data
Your code would only update ONE record that was 30 days out… even if there were multiple
If you only want to update the one >30 and the FARTHEST out then
And ORDER BY has no effect if LIMIT 1
If you had 30, 31, 35, and 40 days… you would get one of them, but no telling WHICH one
The below code, would return only 40 days out
UPDATE MyTable
SET
date = Now()
WHERE
date = (SELECT MAX(date)
FROM MyTable
WHERE DATEDIFF(date, Now()) > 30 AND userid="$userid");
Still the problem it will update ALL records from all users that have this date.
Take the following table filling:
User | Date
------+------------
A | 2021-01-26
B | 2021-01-26
A | 2020-12-20
B | 2020-12-20
A | 2020-11-19
B | 2020-11-19
You have the following data to insert or update: User A, Date 2021-01-27
The rules:
Are there records for user A? - Yes there are
Are there records of more than 30 days old? - Yes there are:
User | Date
------+------------
A | 2020-12-20
A | 2020-11-19
-> Replace the oldest one with the new record.
That would be:
Update TABLE set Date = "2021-01-27"
Where
User = "A"
AND Date = (SELECT MAX(date)
FROM MyTable
WHERE DATEDIFF(date, Now()) > 30 AND userid="A")
You need the Where User = “A” in the update statement too, otherwise the record of user B will be updated too.
This is harder than it should be in MySQL it seems.
UPDATE myTable
SET date=NOW()
WHERE (DATEDIFF(NOW(), date) > 30 AND userid = "$userid")
ORDER BY date DESC
LIMIT 1;
Works perfectly when there’s existing records over 30 days old.
Getting the INSERT to work if it doesn’t find any records seems to be stumping me.
MySQL doesn’t seem to like IF statements.
I might have to use the PHP mysqli_num_rows() function and then do an INSERT if = 0.