mysql - 子查询中用于更新的未知列

Question

我有一个更新语句，我想在单个语句中执行此操作，但我发现它有点棘手。

一张表记录了目标随时间的变化。

例如，从1月1日到31日，目标可以是100，然后从2月1日到28日，目标可以是110，然后从3月1日到31日，目标可以是120，依此类推。

用户只需输入金额和日期。每次删除、更新记录或插入新记录后，都会重新计算每条记录的 date_to。它获取的值是记录的 day_from 的前一天，其中最小的 day_from 大于当前记录的 day_from。由于我想不出一种方法可以使其更清楚，因此这里有一些示例:

From            To            Amount
1st Jan         31st Jan      100
1st Feb         28th Feb      110
1st Mar         31st Mar      120
...
...

如果我插入一条记录，其 date_from 为 1 月 15 日且 value = 105，我希望 1 月 1 日的记录将 date_to 更新为 1 月 14 日，而插入的记录将 date_to 更新为 1 月 31 日

删除 2 月 1 日的记录将使新插入记录的新记录的 date_to 从 1 月 31 日更改为 3 月 28 日。

我希望它能够足够清楚地解释我正在尝试做的事情。

现在，这是我的 SQL，它不起作用

返回结果如下:

Error Code: 1093. You can't specify target table 'ptcth1' for update in FROM clause

update personal_trainer_client_target_history ptcth1 set date_to = 
(
    select min(date_from) from personal_trainer_client_target_history ptcth2 
    where ptcth2.date_from > ptcth1.date_from
)

当我尝试像这样纠正它时，我得到 => 错误代码:1054。“where 子句”中的未知列“ptcth1.date_from”

update personal_trainer_client_target_history ptcth1 set date_to = 
(
  select date_from from 
  (
    select min(date_from) from personal_trainer_client_target_history ptcth2 
    where ptcth2.date_from > ptcth1.date_from
  ) 
  as temp_table
)

有什么想法吗？

Answer 1

    DROP TABLE IF EXISTS my_table;

    CREATE TABLE my_table (from_date DATE NOT NULL PRIMARY KEY,Amount INT NOT NULL);

    INSERT INTO my_table VALUES 
    ('2013-01-01',100),
    ('2013-02-01',110),
    ('2013-03-01',120);

    SELECT x.from_date
         , MIN(y.from_date) - INTERVAL 1 DAY to_date 
         , x.amount
      FROM my_table x 
      LEFT 
      JOIN my_table y 
        ON y.from_date > x.from_date 
     GROUP 
        BY x.from_date;
    +------------+------------+--------+
    | from_date  | to_date    | amount |
    +------------+------------+--------+
    | 2013-01-01 | 2013-01-31 |    100 |
    | 2013-02-01 | 2013-02-28 |    110 |    
    | 2013-03-01 | NULL       |    120 |
    +------------+------------+--------+

    INSERT INTO my_table VALUES ('2013-01-15',105);

    SELECT x.from_date
         , MIN(y.from_date) - INTERVAL 1 DAY to_date 
         , x.amount
      FROM my_table x 
      LEFT 
      JOIN my_table y 
        ON y.from_date > x.from_date 
     GROUP 
        BY x.from_date;
    +------------+------------+--------+
    | from_date  | to_date    | amount |
    +------------+------------+--------+
    | 2013-01-01 | 2013-01-14 |    100 |
    | 2013-01-15 | 2013-01-31 |    105 |
    | 2013-02-01 | 2013-02-28 |    110 |
    | 2013-03-01 | NULL       |    120 |
    +------------+------------+--------+