Deletes not 1 record, but all at once. How to fix?

Question

SQLite

Have a request

DELETE FROM `records` 
WHERE `user_id` in 
(SELECT `user_id` FROM `records` WHERE `user_id` = ? ORDER BY `date` DESC LIMIT 1)

It should delete the last entry, but deletes everything at once. How to fix?

Answer 1

It is necessary to delete not by the user_id condition, but return the id of a specific record and delete it
Something like

DELETE FROM `records` 
WHERE `id` in 
(SELECT `id` FROM `records` WHERE `user_id` = ? ORDER BY `date` DESC LIMIT 1)