Updating two tables at once, but by Union instead of Join

[sffc]

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?

[r937]

UPDATE can only update one table at a time

renumbering your numbers on a regular basis seems wrong, and having two tables for essentially the same thing also seems wrong

both fruits and vegetables are produce, and so you should have only one table for produce

[sigh] but i already know what you're gonna say, that your tables aren't about fruits and vegetables -- well, sorry, but that's how you posted your complex scenario and that's my answer, you need a single produce table

[sffc]

Thanks for the quick response!

You're right, my tables are not actually fruits and vegetables; they're actually Video and Audio. Videos have more information than Audio (like thumbnails, etc), and so I made a separate table for video than I did for audio.

I was thinking that maybe there's a way to use a utility table with this. For example, I have a table that just contains a million rows, each with an integer in it (1, 2, 3, and so on). I have read that UPDATE can update two tables at once using a join; is there some clever way to join both Fruits and Vegetables (or Audio and Video) to the number utility table and update them from there?

[sffc]

I think I found the solution!

When updating multiple tables, you need to join them together horizontally. Well, how about this: join them based on their ordernums to a table that is already collated! This is where you could use the util.numbers table. It contains simply:

+----+
| n  |
+----+
| 1  |
| 2  |
| 3  |
| 4  |
| 5  |
| 6  |
| 7  |
| 8  |
| 9  |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
 
# and so on…
 
+----+

So, just run an update query that joins the Fruits and Vegetable tables to util.numbers:

set @curr:=0;
 
update util.numbers
	left join Fruits on numbers.n=Fruits.ordernum
	left join Vegetables on numbers.n=Vegetables.ordernum
set Fruits.ordernum=@curr:=@curr+2,
	Vegetables.ordernum=@curr:=@curr+2;

And viola! Because MySQL updates the rows in the order that they are presented in util.numbers, our objective is achieved!

One problem with this query: it is slow. The reason I believe that it is slow is because the util.numbers table is huge, like a million rows long. So, how do we get a table that has numbers in the right order that could also match up with the ordernums of Fruits and Vegetables? Use a subquery instead of util.numbers!

set @curr:=0;
 
update (
		select ordernum from Fruits
		union
		select ordernum from Vegetables
		order by ordernum asc
) i1
	left join Fruits on i1.ordernum=Fruits.ordernum
	left join Vegetables on i1.ordernum=Vegetables.ordernum
set Fruits.ordernum=@curr:=@curr+2,
	Vegetables.ordernum=@curr:=@curr+2;

It works, and it's fast, too!