Salocin

Multiple table UPDATE query in MySQL

How do I update a table using one or more other tables in MySQL? The solution is simple, but if you're trying to do it with an UPDATE statement in MySQL versions lower than 4, you're likely to be quite frustrated.

I was recently trying to figure out a way of updating a table in MySQL using a relationship with another table. It turns out that in MySQL versions below 4, it is impossible to reference other tables in an UPDATE statement. This is a problem, and although it’s easily circumvented, I thought I’d write up how it’s done, because two people I’ve spoken to didn’t know.

Here is the problem I faced: I have a table full of order data (OrderTable), with unique IDs for each row. I have a second table (TimeData) which contains information regarding the time and date at which the orders were made, and has the same unique IDs, and I want to insert the data from that table into the original order table, obviously matching the IDs up so that the right order receives the right time. An additional complication is that TimeData does not contain a record for every single record in OrderTable.

TimeData contains two rows : id (INT NOT NULL), and ordertime (DATETIME).

We can see what the final table ought to look like by building it with a SELECT statement :

SELECT OrderTable.*,TimeData.ordertime from OrderTable LEFT OUTER JOIN TimeData ON OrderTable.id=TimeData.id

But what we really want to do is to add the TimeData.ordertime column to OrderTable.

What you might have already tried, which doesn’t work, is the following :

ALTER TABLE OrderTable ADD ordertime DATETIME

UPDATE OrderTable SET ordertime = TimeData.ordertime where OrderTable.id=TimeData.id

The reason this doesn’t work is that UPDATE only takes inputs (in MySQL Versions < 4) from a single table, and is therefore unable (in my experience) to grab data from the TimeData table. The way around this is to create a new table, built from a SELECT statement, as follows :

CREATE TABLE NewOrderTable SELECT OrderTable.*, TimeData.ordertime FROM OrderTable LEFT OUTER JOIN TimeData ON OrderTable.id = TimeData.id

Here’s a brief breakdown of the syntax :

CREATE TABLE NewOrderTable SELECT ...
This creates the new table, and indicates that it will be built from a query.

... OrderTable.* ...
This pulls in the entirety of OrderTable

... ,TimeData.ordertime ...
This pulls in the additional column from TimeData

... FROM OrderTable LEFT OUTER JOIN TimeData ...
This tells us which tables we want, and the type of relationship we want to create between them. A left outer join means we want to pull in all of the rows from the table on the left of the query, and only those rows that have a related record from the table on the right. The reason we need this type of query is to ensure that we pull in every single column from OrderTable. If we just had the two tables listed and a relationship based on the IDs, we would end up with only those rows that were represented in both tables.

... ON OrderTable.id=TimeData.id ...
This tells MySQL what the specific relationship constraint on the two tables is – in this case, rows in TimeData and OrderTable that have the same value in the id field should form part of the same reported row, and will therefore be part of the same row in the new table we are building.

Now all that remains is to rename the old table (OrderTable) to something else, and then replace it by renaming the new table (NewOrderTable) to OrderTable. Keep a copy of the old table so that if anything went wrong, you can reverse the process without losing any data.

This is a very (very) simple application of MySQL commands, but worth examining because they solve a specific problem that often crops up, and because they demonstrate a specific case of certain commands –

• How to use CREATE TABLE to build a new table based on the content of old tables.
  
• How to use the LEFT OUTER JOIN relation, and how it works.
  
• How to get around the single table limit in the UPDATE statement in MySQL versions lower than 4 (most MySQL servers that I run across run 3.x.x).
  

Hope this was helpful, please let me know below if this helped you or if you have additional questions.

Posted by nlvp at December 13, 2003 02:21 PM