Need to do 2 requests:

  1. Delete a certain number of table entries.
  2. Extract records from one table and insert them into another.

In the case of the usual sampling of the necessary values ​​in the documentation, it is recommended to use LIMIT and it looks like this:

 SELECT * FROM tab.name LIMIT 10 (например 10);

It works, but in the case of deletion it does not work, the insertion also:

 SELECT * FROM tab.name LIMIT 10; INSERT INTO tab.name1 (SELECT FROM tab.name2 LIMIT 10);

How to write a request to implement these conditions?

    1 answer 1

    DELETE with limit:

     DELETE FROM tablename WHERE primary_key IN ( SELECT primary_key FROM tablename ORDER BY somefield LIMIT 10 FOR UPDATE )

    Copy to another table:

     INSERT INTO tablename (/*columns*/) SELECT /*data for columns*/ FROM tablename2 ORDER BY somefield LIMIT 10

    If you need to transfer data to another table, i.e. in one to delete and insert in another, then this can be done with one CTE, like this:

     WITH move_data AS ( SELECT primary_key /* и какие ещё нужны данные */ FROM tablename ORDER BY somefield LIMIT 10 FOR UPDATE ), delete_old AS ( DELETE FROM tablename WHERE primary_key IN ( SELECT primary_key FROM move_data ) ) INSERT INTO tablename (/*columns*/) SELECT /*data for columns*/ FROM move_data