SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast sffc's Avatar
    Join Date
    Jul 2006
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    I have a query that I currently use to standardize the collation in a single MySQL table:
    Code MySQL:
    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:
    Code MySQL:
    mysql> select MyID, ordernum from MyTable;
    +------+----------+
    | MyID | ordernum |
    +------+----------+
    | 1    | 3        |
    | 2    | 2        |
    | 3    | 5        |
    +------+----------+
    to this:
    Code MySQL:
    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:
    Code MySQL:
    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:
    Code MySQL:
    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:
    Code MySQL:
    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:
    Code MySQL:
    +----+-------+
    | 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?
    "I haven't failed, I just found
    100,000 ways that don't work"
    — Thomas Edison

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast sffc's Avatar
    Join Date
    Jul 2006
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    "I haven't failed, I just found
    100,000 ways that don't work"
    — Thomas Edison

  4. #4
    SitePoint Enthusiast sffc's Avatar
    Join Date
    Jul 2006
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code MySQL:
    +----+
    | 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:
    Code MySQL:
    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!
    Code MySQL:
    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!
    "I haven't failed, I just found
    100,000 ways that don't work"
    — Thomas Edison


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •