sql - 如何更新日期类型列？

Question

update answers a1 
   set a1.FilingDate=(
                       select date_sub( a2.FilingDate
                                       ,Interval 1000 Year) as FilingDate
                       from answers a2 
                       where Year(a2.FilingDate)>=3000
                     )
where Year(a1.FilingDate)>=3000

但它给了我以下错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update answers a1 set a1.FilingDate=( select date_sub(a2.FilingDate,Interval 10' at line 1

谁能告诉我这个问题及其解决方案？

Answer 1

您的子查询可以在 A1 中的每一行返回多个值，MySQL 不知道它应该从 A2 中选择哪个值!

我认为您的查询也可以是:

update answers a1 
   set a1.FilingDate=date_sub( a1.FilingDate,Interval 1000 Year)
where Year(a1.FilingDate)>=3000

注意在备份上试试这个!