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?