mysql - UPDATE INNER JOIN SELECT STATEMENT 不起作用。我的语法正确吗？

Question

我正在开发一个数据库，该数据库将使用状态表中的数据总和自动更新总计表中的数据。我需要按日期完成计算。我希望每个 tail_no 每天每个字段都有一个总数。每次我使用下面的代码时，我都会收到语法错误。

CREATE TABLE  pc12_status (hobbs_start decimal(5,2) NOT NULL, 
                       hobbs_end decimal(5,2) NOT NULL PRIMARY KEY,
                       tail_no int(5) NOT NULL,
                       landings int(5) NOT NULL,
                       engine_cycles int(5) NOT NULL,
                       flight_date date NOT NULL);



CREATE TABLE pc12_totals (flight_hours decimal (5,2) NOT NULL,
                          landings_total int(5) NULL,
                          engine_cycles int(5) NULL,
                          flight_date date NOT NULL,
                          tail_no int(5) NOT NULL,
                          PRIMARY KEY (tail_no, flight_date));

UPDATE pc12_totals
SET pc12_totals.flight_hours = pc12_status.flight_hours,
pc12_totals.landings_total = pc12_status.landings,
pc12_totals.engine_cycles = pc12_status.engine_cycles, 
pc12_totals.flight_date = pc12_status.flight_date,
pc12_totals.tail_no = pc12_status.tail_no
FROM pc12_status
INNER JOIN (SELECT SUM(hobbs_end - hobbs_start) flight_hours,
            SUM (landings) landings_total, sum(engine_cycles) engine_cycles,
            flight_date,
            tail_no
            FROM pc12_status
            GROUP BY flight_date) 
            pc12_totals ON pc12_totals.tail_no = pc12_status.tail_no;


INSERT INTO pc12_status VALUES (1.7, 1.9, 1378, 2, 1, "1987-12-17");
INSERT INTO pc12_status VALUES (1.9, 2.8, 1378, 2, 1, "1987-12-17");
INSERT INTO pc12_status VALUES (2.8, 4.5, 1378, 2, 1, "1987-12-17");
INSERT INTO pc12_status VALUES (4.5, 6.7, 1378, 2, 1, "1987-12-18");
INSERT INTO pc12_status VALUES (6.7, 7.4, 1378, 2, 1, "1987-12-18");
INSERT INTO pc12_status VALUES (7.4, 8.9, 1378, 2, 1, "1987-12-19");

Answer 1

使用连接更新的语法:对于一般更新加入:

   UPDATE TABLEA a 
   JOIN TABLEB b ON a.join_colA = b.join_colB  
   SET a.columnToUpdate = [something]

所以您更正的查询是:

UPDATE pc12_totals
JOIN (      
            SELECT SUM(hobbs_end - hobbs_start) flight_hours,
            SUM (landings) landings_total, sum(engine_cycles) engine_cycles,
            flight_date,
            tail_no
            FROM pc12_status
            GROUP BY flight_date
    ) AS
    pc12_status ON pc12_totals.tail_no = pc12_status.tail_no
SET pc12_totals.flight_hours = pc12_status.flight_hours,
pc12_totals.landings_total = pc12_status.landings,
pc12_totals.engine_cycles = pc12_status.engine_cycles, 
pc12_totals.flight_date = pc12_status.flight_date,
pc12_totals.tail_no = pc12_status.tail_no