SQL help INSERT/UPDATE/REPLACE

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

Delete from TABLE where date < NOW-30;
Insert INTO TABLE values(userid,date) select 'BRIAN', NOW;

or whatever qualifies for now in your db…

That deletes all record older than 30 days …

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;

Thats right.

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.

Theo’s process should accomplish this.

2 Likes

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)

I wasn’t planning on having an index, unless that’s needed for some reason…

Not sure why that would be preferable over the REPLACE command, but we digress…

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, @Theo 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

1 Like

No fields are unique. Are non-unique indexes useful?

a unique combination of fields would work as well

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.

Are you sure? Seems to work properly in my case. It sorts and then returns the top value when I tested it…

The MySQL function is ROW_COUNT(), so the if statement should be something like:

IF ROW_COUNT() = 0 THEN

IF, BEGIN, END all seem to have issues when doing queries via phpMyAdmin with mySQL. Not sure why…