Mega Code Archive
When you update values in a joined table, you can update more than one value at a time
<![CDATA[
mysql>
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.00 sec)
mysql>
mysql>
mysql> INSERT INTO Books
-> VALUES (101, 'C: Writing on Writing', 12),
-> (102, 'Oracle', 17),
-> (103, 'Opera', 23),
-> (104, 'Notebook', 32),
-> (105, 'Pascal', 6),
-> (106, 'One Hundred Years of Solitude', 28);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
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.01 sec)
mysql>
mysql> INSERT INTO Orders VALUES (1001, 103, 1, '2004-10-12 12:30:00'),
-> (1002, 101, 1, '2004-10-12 12:31:00'),
-> (1003, 103, 2, '2004-10-12 12:34:00'),
-> (1004, 104, 3, '2004-10-12 12:36:00'),
-> (1005, 102, 1, '2004-10-12 12:41:00'),
-> (1006, 103, 2, '2004-10-12 12:59:00'),
-> (1007, 101, 1, '2004-10-12 13:01:00'),
-> (1008, 103, 1, '2004-10-12 13:02:00'),
-> (1009, 102, 4, '2004-10-12 13:22:00'),
-> (1010, 101, 2, '2004-10-12 13:30:00'),
-> (1011, 103, 1, '2004-10-12 13:32:00');
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
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>
mysql>
mysql> drop table Orders;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table books;
Query OK, 0 rows affected (0.00 sec)
]]>