Update two tables in one update statement : Update « Insert Update Delete « MySQL Tutorial






  1. MySQL Tutorial
  2. Insert Update Delete
  3. Update
mysql>
mysql> CREATE TABLE Books(
    ->    BookID SMALLINT NOT NULL PRIMARY KEY,
    ->    BookName VARCHAR(40) NOT NULL,
    ->    InStock SMALLINT NOT NULL
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> CREATE TABLE Orders(
    ->    OrderID SMALLINT NOT NULL PRIMARY KEY,
    ->    BookID SMALLINT NOT NULL,
    ->    Quantity TINYINT (40) NOT NULL DEFAULT 1,
    ->    DateOrdered TIMESTAMP,
    ->    FOREIGN KEY (BookID) REFERENCES Books (BookID)
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql>
mysql> INSERT INTO Books VALUES (101, 'Java', 12),
    ->                          (102, 'PHP', 17),
    ->                          (103, 'MySQL', 23),
    ->                          (104, 'Perl', 32),
    ->                          (105, 'Pyton', 6),
    ->                          (106, 'www.java2s.com', 28);
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO Orders VALUES (1001, 103, 1, '2001-11-12 12:30:00'),
    ->                           (1002, 101, 1, '2002-10-16 12:31:00'),
    ->                           (1003, 103, 2, '2003-02-11 12:34:00'),
    ->                           (1004, 104, 3, '2004-01-19 12:36:00'),
    ->                           (1005, 102, 1, '2005-12-17 12:41:00'),
    ->                           (1006, 103, 2, '2006-10-18 12:59:00'),
    ->                           (1007, 101, 1, '2004-11-21 13:01:00'),
    ->                           (1008, 103, 1, '2014-10-16 13:02:00'),
    ->                           (1009, 102, 4, '1994-09-02 13:22:00'),
    ->                           (1010, 101, 2, '1995-10-04 13:30:00'),
    ->                           (1011, 103, 1, '1996-08-12 13:32:00'),
    ->                           (1012, 105, 1, '2004-10-03 13:40:00'),
    ->                           (1013, 106, 2, '2002-05-12 13:44:00'),
    ->                           (1014, 103, 1, '2001-10-01 14:01:00'),
    ->                           (1015, 106, 1, '1997-05-05 14:05:00'),
    ->                           (1016, 104, 2, '1998-10-07 14:28:00'),
    ->                           (1017, 105, 1, '2004-03-12 14:31:00'),
    ->                           (1018, 102, 1, '2004-10-21 14:32:00'),
    ->                           (1019, 106, 3, '1991-01-30 14:49:00'),
    ->                           (1020, 103, 1, '1990-10-12 14:51:00');
Query OK, 20 rows affected (0.05 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from Books;
+--------+----------------+---------+
| BookID | BookName       | InStock |
+--------+----------------+---------+
|    101 | Java           |      12 |
|    102 | PHP            |      17 |
|    103 | MySQL          |      23 |
|    104 | Perl           |      32 |
|    105 | Pyton          |       6 |
|    106 | www.java2s.com |      28 |
+--------+----------------+---------+
6 rows in set (0.00 sec)

mysql>
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered         |
+---------+--------+----------+---------------------+
|    1001 |    103 |        1 | 2001-11-12 12:30:00 |
|    1002 |    101 |        1 | 2002-10-16 12:31:00 |
|    1003 |    103 |        2 | 2003-02-11 12:34:00 |
|    1004 |    104 |        3 | 2004-01-19 12:36:00 |
|    1005 |    102 |        1 | 2005-12-17 12:41:00 |
|    1006 |    103 |        2 | 2006-10-18 12:59:00 |
|    1007 |    101 |        1 | 2004-11-21 13:01:00 |
|    1008 |    103 |        1 | 2014-10-16 13:02:00 |
|    1009 |    102 |        4 | 1994-09-02 13:22:00 |
|    1010 |    101 |        2 | 1995-10-04 13:30:00 |
|    1011 |    103 |        1 | 1996-08-12 13:32:00 |
|    1012 |    105 |        1 | 2004-10-03 13:40:00 |
|    1013 |    106 |        2 | 2002-05-12 13:44:00 |
|    1014 |    103 |        1 | 2001-10-01 14:01:00 |
|    1015 |    106 |        1 | 1997-05-05 14:05:00 |
|    1016 |    104 |        2 | 1998-10-07 14:28:00 |
|    1017 |    105 |        1 | 2004-03-12 14:31:00 |
|    1018 |    102 |        1 | 2004-10-21 14:32:00 |
|    1019 |    106 |        3 | 1991-01-30 14:49:00 |
|    1020 |    103 |        1 | 1990-10-12 14:51:00 |
+---------+--------+----------+---------------------+
20 rows in set (0.00 sec)

mysql>
mysql>
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.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql>
mysql> select * from Books;
+--------+----------------+---------+
| BookID | BookName       | InStock |
+--------+----------------+---------+
|    101 | Java           |      10 |
|    102 | PHP            |      17 |
|    103 | MySQL          |      23 |
|    104 | Perl           |      32 |
|    105 | Pyton          |       6 |
|    106 | www.java2s.com |      28 |
+--------+----------------+---------+
6 rows in set (0.00 sec)

mysql>
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered         |
+---------+--------+----------+---------------------+
|    1001 |    103 |        1 | 2001-11-12 12:30:00 |
|    1002 |    101 |        3 | 2007-07-23 19:09:39 |
|    1003 |    103 |        2 | 2003-02-11 12:34:00 |
|    1004 |    104 |        3 | 2004-01-19 12:36:00 |
|    1005 |    102 |        1 | 2005-12-17 12:41:00 |
|    1006 |    103 |        2 | 2006-10-18 12:59:00 |
|    1007 |    101 |        1 | 2004-11-21 13:01:00 |
|    1008 |    103 |        1 | 2014-10-16 13:02:00 |
|    1009 |    102 |        4 | 1994-09-02 13:22:00 |
|    1010 |    101 |        2 | 1995-10-04 13:30:00 |
|    1011 |    103 |        1 | 1996-08-12 13:32:00 |
|    1012 |    105 |        1 | 2004-10-03 13:40:00 |
|    1013 |    106 |        2 | 2002-05-12 13:44:00 |
|    1014 |    103 |        1 | 2001-10-01 14:01:00 |
|    1015 |    106 |        1 | 1997-05-05 14:05:00 |
|    1016 |    104 |        2 | 1998-10-07 14:28:00 |
|    1017 |    105 |        1 | 2004-03-12 14:31:00 |
|    1018 |    102 |        1 | 2004-10-21 14:32:00 |
|    1019 |    106 |        3 | 1991-01-30 14:49:00 |
|    1020 |    103 |        1 | 1990-10-12 14:51:00 |
+---------+--------+----------+---------------------+
20 rows in set (0.02 sec)

mysql>
mysql>
mysql>
mysql> drop table Orders;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> drop table Books;
Query OK, 0 rows affected (0.05 sec)

mysql>








7.4.Update
7.4.1.Syntac for UPDATE statement
7.4.2.UPDATE-ing Records
7.4.3.Change multiple columns using one statement
7.4.4.Update a value based on the value it currently holds
7.4.5.Use the LIMIT function to control the number of rows that are affected by your UPDATE statement
7.4.6.Update two tables in one update statement
7.4.7.Update only 5 records