Delete old rows if there are up to date ones

(1) By Alex_Don on 2022-10-06 19:02:29 [link] [source]

I have the following "game" table which contains the following information:

date_creation date_update   player rang      score
2022-09-11    2022-09-22    dog    medium    30
2022-09-10    2022-09-22    cat    medium    40
2022-09-11    2022-09-23    dog    medium    40
2022-09-11    2022-09-25    dog    medium    50
2022-09-10    2022-09-26    cat    medium    65
2022-09-15    2022-09-22    hors    low      10

> SELECT * FROM game ORDER BY player, date_update

date_creation date_update   player rang      score
2022-09-10    2022-09-22    cat    medium    40
2022-09-10    2022-09-26    cat    medium    65
2022-09-11    2022-09-22    dog    medium    30
2022-09-11    2022-09-23    dog    medium    40
2022-09-11    2022-09-25    dog    medium    50
2022-09-15    2022-09-22    hors    low      10

and try to remove rows from it if the player contains the up-to-date data (see date_update column); ie to get in the table only:

date_creation date_update   player rang      score
2022-09-11    2022-09-25    dog    medium    50
2022-09-10    2022-09-26    cat    medium    65
2022-09-15    2022-09-22    hors    low      10

These rows for the player "dog" must be deleted because there is entry from "2022-09-25", and there is no need to keep the old records:

2022-09-11    2022-09-22    dog    medium    30
2022-09-11    2022-09-23    dog    medium    40

This row for the player "cat" must be deleted because there is entry from "2022-09-26":

2022-09-10    2022-09-22    cat    medium    40

Please give me some ideas on which direction I should move in. As far as I understand it shouldn't be complicated, but for some reason I am confused.

(2) By Jim Morris (jimmorris33215) on 2022-10-06 19:25:48 in reply to 1 [link] [source]

If the old records are without value, perhaps you should consider the player a unique key and simply update the rang and score for a player instead of doing inserts. This also preserves the initial data_creation date, so it is meaningful.

(3) By Simon Slavin (slavin) on 2022-10-06 20:27:25 in reply to 1 [source]

If you don't need to keep the old data at all, just make the player a UNIQUE index to that table, then use INSERT OR REPLACE INTO ….

(4) By Alex_Don on 2022-10-06 20:33:29 in reply to 1 [link] [source]

Thank you for these tips, I understand that the original way is not the proper one; unfortunately at the moment I have to work with this particular table, which needs to be cleared of unnecessary data.

(6) By Simon Slavin (slavin) on 2022-10-07 13:24:23 in reply to 4 [link] [source]

Ah, you could have explained that in your original post. We thought you had control over the database.

Michael A. Cleverly's response (5) should work perfectly.

(5) By Michael A. Cleverly (cleverly) on 2022-10-06 20:46:46 in reply to 1 [link] [source]

DELETE FROM game 
 WHERE rowid NOT IN (SELECT rowid 
                       FROM game 
                      GROUP BY player 
                     HAVING max(date_update));

But, like others have suggested, defining a primary key on game and then doing and INSERT OR REPLACE INTO is better.

(7.3) By Keith Medcalf (kmedcalf) on 2022-10-07 14:42:59 edited from 7.2 in reply to 5 [link] [source]

These will, of course, only work if (player, date_update) is unique. As soon as it is not unique (in particular has multiple rows with max(date_update) for the same player), the process will fail. Unless (player, date_update) is unique (and max(date_update) always indicates the last update made for a player), there is no way to tell what update is the "last one" that was made.

  select *
    from game
group by player
  having max(rowid)
;

has a better chance of being correct, assuming one is not farting with the rowid's but letting them assign naturally.

But it is still only a very thin chance of being correct.