更新语句错误，到底为啥呀 ，各位帮忙看看！

Question

   执行如下语句  update payment set end_date=date_add(end_date,interval  0 day)
                         where payment_id=(select payment_id from payment where date(now())>=date(begin_date)
                                                                          and date(now())<=date(end_date)
                                                                          and cust_id='0020001081100047'
                                                                    and Operation_Type='1');  老是报you can't specify target table 'payment' for update in  from clause错误。为啥？

Answer 1

update payment a inner join payment b
on a.payment_id=b.payment_id
set end_date=date_add(end_date,interval  0 day) 
where date(now())>=date(b.begin_date)                                                                          and date(now()) <=date(b.end_date)
and b.cust_id='0020001081100047'
and b.Operation_Type='1'

Answer 2

where条件设置错误，
错误的原因是you can't specify target table 'payment' for update in  from clause
翻译如下，你应该就明白了，你不能指定目标表payment来使用from的更新条件

Answer 3

搜索一下，老问题了。
http://answers.google.com/answers/threadview?id=475696

Answer 4

update payment a inner join payment b 
on a.payment_id=b.payment_id 
set a.end_date=date_add(a.end_date,interval  0 day) 
where date(now())>=date(b.begin_date) and date(now()) <=date(b.end_date) 
and b.cust_id='0020001081100047' 
and b.Operation_Type='1'