0

I'm trying to solve this question Delete all but top n from database table in SQL but the accepted answer's subquery is executed for every row in the table.

Does this happen to my attempt? The subquery could change depending on what rows get deleted. If so how can you fix that?

DELETE FROM patients
WHERE patient_id >=
(SELECT patient_id FROM patients
ORDER BY patient_id
LIMIT 1 OFFSET 10);

SELECT * FROM patients;

I was using the data from https://www.sql-practice.com/

Tom Huntington
  • 2,260
  • 10
  • 20
  • 2
    To answer such questions, you really need to learn to use the tools your database system provides for examining the *plan* that the optimizer has arrived at. The plan the optimizer produces can vary for a great many reasons which means it's not usually possible to give a conclusive answer by just inspecting the SQL. The optimizer also makes trade-offs such that it may not produce an *absolutely* optimal plan but one which is "good enough", which may include performing some apparently redundant activities. – Damien_The_Unbeliever May 25 '23 at 07:13
  • 2
    Also, there's no guarantee that the accepted answer on the linked question runs the subquery for every row. There's some comments making such a bald assertion but little evidence of it. *most* database systems, when presented with an *uncorrelated* subquery will most often only execute it once only, not once per row. But again, you'd need to inspect plans to know for certain whether it's so. – Damien_The_Unbeliever May 25 '23 at 07:23
  • @Damien_The_Unbeliever thanks, I'll look into examining the execution plans – Tom Huntington May 25 '23 at 07:36

0 Answers0