I want to add Total_Price from Food_Order table and Drinks_Order table into Order table. However, it gave me Error: #1242 - Subquery returns more than 1 row.
The coding to add values from 2 tables:
UPDATE `ORDER`
SET Total_Price =
(SELECT Total_Price FROM FOOD_ORDER WHERE FOOD_ORDER.Order_ID
=`ORDER`.Order_ID) + (SELECT Total_Price FROM DRINKS_ORDER WHERE
DRINKS_ORDER.Order_ID = `ORDER`.Order_ID);
Coding for FOOD_ORDER,DRINKS_ORDER and ORDER table :
INSERT INTO `ORDER` (`Order_ID`, `Pay_Method`, `Pay_status`, `Order_Time`, `Cus_ID`, `Emp_ID`)VALUES
('1000000001','CASH','PAID','2015-09-05 12:58:51','100000','10000006'),
('1000000002','CASH','PAID','2015-09-05 14:45:00','100001','10000009'),
('1000000003','CREDIT CARD','PAID','2015-09-05 16:34:21','100002','10000002'),
('1000000004','CASH','PAID','2015-09-05 17:10:10','100003','10000009'),
('1000000005','CREDIT CARD','PAID','2015-09-06 12:33:11','100004','10000007'),
('1000000006','CREDIT CARD','PAID','2015-09-06 15:51:15','100005','10000004'),
('1000000007','CASH','PAID','2015-09-06 16:20:05','100006','10000003'),
('1000000008','','UNPAID','2015-09-06 17:00:00','100007','10000006'),
('1000000009','','UNPAID','2015-09-06 17:23:15','100008','10000002'),
('1000000010','','UNPAID','2015-09-06 17:30:34','100009','10000007')
INSERT INTO `FOOD_ORDER` VALUES
('F000001','3','39.90','1000000001','201'),
('F000002','1','22.50','1000000002','108'),
('F000003','2','25.40','1000000003','203'),
('F000004','2','25.20','1000000004','101'),
('F000005','2','26.60','1000000005','202'),
('F000006','1','30.40','1000000006','103'),
('F000007','1','30.40','1000000007','106'),
('F000008','2','7.60','1000000008','803'),
('F000009','2','26.60','1000000009','202'),
('F000010','4','16.00','1000000010','801')
INSERT INTO `DRINKS_ORDER` VALUES
('D000001','2','16.00','1000000001','907'),
('D000002','1','4.00','1000000001','903'),
('D000003','1','3.00','1000000003','905'),
('D000004','2','16.00','1000000005','908'),
('D000005','2','6.00','1000000006','901'),
('D000006','2','6.00','1000000006','902'),
('D000007','1','3.00','1000000008','905'),
('D000008','2','6.00','1000000009','906'),
('D000009','1','3.00','1000000010','904')
The Order_ID in FOOD_ORDER and in DRINKS_ORDER is not unique. If you want to add all prices, use SUM:
UPDATE `ORDER`
SET Total_Price = (SELECT SUM (Total_Price) FROM FOOD_ORDER
WHERE FOOD_ORDER.Order_ID =`ORDER`.Order_ID) +
(SELECT SUM (Total_Price) FROM DRINKS_ORDER
WHERE DRINKS_ORDER.Order_ID = `ORDER`.Order_ID);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments