sort same field from different tables

Question

I have somes tables (around 20) with the same structure and I'm trying to sort them with a php script and insert them in a new table with the cheapest price in cheapest1, then cheapest2 for more expensive... and the most expensive in column cheapest20:

table A: id
name
price

table B: id
name
price

table X: id
name
price

tableResult: id
name
cheapest1
price1
cheapest2
price2
...
cheapestX
priceX

My code so far is:

(SELECT id, price, name FROM tableA WHERE id = $id)
UNION
(SELECT id, price, name FROM tableB WHERE id = $id)
ORDER BY price ASC

I have been looking for different solutions but it takes too long to SELECT for 15000 rows so I guess there is another way to do it. I haven't looked for the update query yet, I need to fix the select in the first time.

Any suggestion?

EDIT: clarified question, with more tables

EDIT2: solution

I finally got it right. This is the query to select the cheapest: I select each id and I browse:

(SELECT price AS P1, name, id FROM tableA WHERE id = ?) UNION (SELECT price AS P1, name, id FROM tableB WHERE id = ?) UNION (SELECT price AS P1, name, id FROM tableC WHERE id = ?) ORDER BY P1 ASC

Then I Insert in the new table as glglgl suggested:

('INSERT INTO table (id, name, Position, price) VALUES (?, ?, ?, ?) ');

Answer 1

If you have control over the final structure of the tables: Don't do that. Instead, use only one table and add a field for indicating which purpose it serves.

The target table is not structured well either. Instead, you should use

tableResult:
 id
 name
 cheapestorder
 cheapest
 price

which makes all easier.

Thus, instead of having one row containing

id=10, name=foo, cheapest1=a, cheapestprice1=10, cheapest2=b, cheapestprice2=13,

you have several rows

id=10, name=foo, cheapestorder=1, cheapest=a, cheapestprice=10
id=10, name=foo, cheapestorder=2, cheapest=b, cheapestprice=13

(This process is called "normalization" in database theory.)

Putting all input tables into one simplifies dcp's query:

SELECT name,
       max(mxprice) mxprice,
       min(mnprice) mnprice
FROM
(
  SELECT name,
       max(price) mxprice,
       min(price) mnprice
    FROM tableABC
    GROUP BY NAME, tbltag
) a
GROUP BY NAME

or maybe even just

SELECT name,
       max(price) mxprice,
       min(price) mnprice
    FROM tableABC
    GROUP BY NAME

.