INSERT and DELETE in single operation (moving from one table to other)

Stemming from this question, I am going to use that complex sort's results to decide which rows' data to move from one table to another. Though I have not yet tried it, I think there should be no problem to do the "copy" part via a:

INSERT * FROM (...)

Using my results from that SELECT inside the parentheses.

However, if I then user the same query to try to SELECT the same results in order to do a DELETE FROM, the results will no longer include those same rows. So, I need some way to store at least the primary keys (and origin table, since we are talking about 2 origin tables and one destination table) in order to DELETE the correct row from the correct table.

From what I have read and understood, it's not possible to do in a single query, and in any case would require a transaction (which I need to lookup how to correctly do).

So my first approach would be to do the INSERT copy first, and then maybe delete form the two tables any rows containing primary keys appearing in my destination table. It seems a little inefficient since that table would be much larger than the two origin tables, and I figure that from the derived table I got from my subquery, it would be much much more efficient to use the info located there. So, any clues as to how I could best go about this?

Insert everything into a temp table first. Then your insert and delete are simply everything from the temp table rather than your complicated query. This solves your problem of how to do both halves and is probably going to perform better than running your complicated query multiple times.

I do more MS-SQL than My-SQL so I'll link to another answer on Stack Overflow.

https://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table

On MS-SQL it is simply

SELECT .... into #MyTempTable FROM ... WHERE..

StackExchange Database Administrators Q#143637, answer score: 2