php - 在同一张表中插入和更新

Question

我有 table

table1
****************************
id | start_date | end_date  
1    30/06/2012   01/01/9999

当我插入一个新行时

id | start_date | end_date
1    30/06/2012   26/06/2012 <- Now it is the start date of the next row2
2    26/06/2012   01/01/9999

插入行的开始日期将成为上一行的结束日期，依此类推。

 $sql = "INSERT INTO 
                    table1(cate_id,task_id,obj,end_date,start_date,line)
                    SELECT 
                        $cate_id ,
                        $task_id,
                        $obj,
                        '2011-02-28', -> this what I am trying to do
                        '9999-01-01', -> this what I am trying to do
                        line1 
                    FROM 
                        line_task
                    WHERE 
                         line_task_id = (
                                            SELECT 
                                                line_task_id 
                                            FROM 
                                                task 
                                            WHERE 
                                                task_id = 2
                                           )";

当我尝试这样的事情时:

insert into table1(cate_id,task_id,obj, start_date, end_date,line) 
values (4,3,23, (Select max(end_date) from table1),end_date,'value_line');

我收到消息:

  Error code 1093, SQL state HY000: You can't specify target table 'table1' for update     in FROM clause
Line 1, column 1

0 秒后执行完成，发生 1 个错误

可以告诉我如何使用 mysql 执行此操作吗？谢谢

Answer 1

试试这个:

  Set @max_date = Select max(end_date) from table;

    Insert into table(id, start_date, end_date) values (id, @max_date ,end_date);