I have a query that I currently use to standardize the collation in a single MySQL table:
set @curr:=0;
update MyTable set ordernum=@curr:=@curr+2 where ordernum>=0 order by ordernum asc
That query takes a table with irregular values in the “ordernum” field and makes them all multiples of 2. For example, it would change this table:
mysql> select MyID, ordernum from MyTable;
+------+----------+
| MyID | ordernum |
+------+----------+
| 1 | 3 |
| 2 | 2 |
| 3 | 5 |
+------+----------+
to this:
mysql> select MyID, ordernum from MyTable;
+------+----------+
| MyID | ordernum |
+------+----------+
| 1 | 4 |
| 2 | 2 |
| 3 | 6 |
+------+----------+
2 was lowest, so it became 2; 3 was second-lowest, so it became 4; and 5 was third-lowest, so it became 6.
I now need to expand that query to two tables. I have two tables, say, Fruits and Vegetables:
mysql> select FruitID, ordernum from Fruits;
+---------+----------+
| FruitID | ordernum |
+---------+----------+
| 1 | 3 |
| 2 | 6 |
| 3 | 5 |
+---------+----------+
mysql> select VegID, ordernum from Vegetables;
+-------+----------+
| VegID | ordernum |
+-------+----------+
| 1 | 4 |
| 2 | 1 |
| 3 | 8 |
| 4 | 9 |
+-------+----------+
The "ordernum"s for Fruits and Vegetables are related, and I need to run my original query, but it needs to update both tables.
In this example, the current ordernums, when Fruits and Vegetables are combined, are: 3, 6, 5, 4, 1, 8, 9
They all need to be even numbers in sequence. 1 is lowest; it becomes 2. 3 is second-lowest; it becomes 4. 4 is third-lowest; it becomes 6, and so on, until we get:
4, 10, 8, 6, 2, 12, 14
In table form, that would be:
mysql> select FruitID, ordernum from Fruits;
+---------+----------+
| FruitID | ordernum |
+---------+----------+
| 1 | 4 |
| 2 | 10 |
| 3 | 8 |
+---------+----------+
mysql> select VegID, ordernum from Vegetables;
+-------+----------+
| VegID | ordernum |
+-------+----------+
| 1 | 6 |
| 2 | 2 |
| 3 | 12 |
| 4 | 14 |
+-------+----------+
The important thing is that when the data are selected and ordered by “ordernum”, the result is the same both before and after the query is run. In other words, the query:
select FruitID as ID, "Fruit" as type from Fruits
UNION
select VegID as ID, "Veg" as type from Vegetables
ORDER BY ordernum ASC
should return:
+----+-------+
| ID | type |
+----+-------+
| 2 | Veg |
| 1 | Fruit |
| 1 | Veg |
| 3 | Fruit |
| 2 | Fruit |
| 3 | Veg |
| 4 | Veg |
+----+-------+
and it should return the result set in that order both before and after the update query is run.
What update query can achieve this objective with both the Fruits and the Vegetables table?