MySQL Update Two Tables With Join Condition


/*
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered         |
+---------+--------+----------+---------------------+
|    1001 |    103 |        1 | 2004-01-12 12:30:00 |
|    1002 |    101 |        1 | 2001-02-12 12:31:00 |
|    1003 |    103 |        2 | 2002-03-12 12:34:00 |
|    1004 |    104 |        3 | 2003-04-12 12:36:00 |
|    1005 |    102 |        1 | 2004-05-12 12:41:00 |
|    1006 |    103 |        2 | 2001-06-12 12:59:00 |
|    1007 |    101 |        1 | 2002-07-12 13:01:00 |
|    1008 |    103 |        1 | 2003-08-12 13:02:00 |
|    1009 |    102 |        4 | 2004-09-12 13:22:00 |
|    1010 |    101 |        2 | 2005-11-12 13:30:00 |
|    1011 |    103 |        1 | 2006-12-12 13:32:00 |
|    1012 |    105 |        1 | 2001-02-12 13:40:00 |
|    1013 |    106 |        2 | 2002-04-12 13:44:00 |
|    1014 |    103 |        1 | 2003-06-12 14:01:00 |
|    1015 |    106 |        1 | 2005-01-12 14:05:00 |
|    1016 |    104 |        2 | 2003-11-12 14:28:00 |
|    1017 |    105 |        1 | 2002-03-12 14:31:00 |
|    1018 |    102 |        1 | 2001-05-12 14:32:00 |
|    1019 |    106 |        3 | 2003-07-12 14:49:00 |
|    1020 |    103 |        1 | 2004-01-12 14:51:00 |
+---------+--------+----------+---------------------+
20 rows in set (0.00 sec)
mysql> select * from Books;
+--------+-----------+---------+
| BookID | BookName  | InStock |
+--------+-----------+---------+
|      1 | Poet      |    1934 |
|      2 | Ohio      |    1919 |
|      3 | Angels    |    1966 |
|      4 | Black     |    1932 |
|    101 | Writing   |       9 |
|    102 | News      |      17 |
|    103 | Angels    |      23 |
|    104 | Poet      |      32 |
|    105 | Dunces    |       6 |
|    106 | Solitude  |      28 |
|    107 | Postcards |    1992 |
|    108 | The       |    1993 |
+--------+-----------+---------+
12 rows in set (0.00 sec)
mysql> UPDATE Books, Orders
-> SET Orders.Quantity=Orders.Quantity+2,
->    Books.InStock=Books.InStock-2
-> WHERE Books.BookID=Orders.BookID
->    AND Orders.OrderID = 1002;
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2  Changed: 2  Warnings: 0

I used this example to update a column of one table from data from another table in the same database. In one example where I added a new column in a table but I needed that column to be populated from data from another table.

About onlinejt

Blogger

,

No comments yet.

Leave a Reply

*