Mega Code Archive

 

Categories / MySQL / Insert Delete Update

 

Update records with calculation based on two tables

/* mysql> select * from Books; +--------+-----------+---------+ | BookID | BookName  | InStock | +--------+-----------+---------+ |      1 | Poet      |    1934 | |      2 | Ohio      |    1919 | |      3 | Angels    |    1966 | |      4 | Black     |    1932 | |    101 | Writing   |      10 | |    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 Books.InStock=Books.InStock-Orders.Quantity     -> WHERE Books.BookID=Orders.BookID     ->    AND Orders.OrderID=1002; Query OK, 1 row affected (0.08 sec) Rows matched: 1  Changed: 1  Warnings: 0 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.01 sec) */ Drop table Books; Drop table Orders;         CREATE TABLE Books (    BookID SMALLINT NOT NULL PRIMARY KEY,    BookName VARCHAR(40) NOT NULL,    InStock SMALLINT NOT NULL ) ENGINE=INNODB; 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; INSERT INTO Orders VALUES (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'); INSERT INTO Books VALUES (101, 'Writing', 12),                          (102, 'News', 17),                          (103, 'Angels', 23),                          (104, 'Poet', 32),                          (105, 'Dunces', 6),                          (106, 'Solitude', 28); select * from Books; UPDATE Books, Orders SET Books.InStock=Books.InStock-Orders.Quantity WHERE Books.BookID=Orders.BookID    AND Orders.OrderID=1002; select * from Books;