how do I update the date type column?

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

but it gives me the following error:

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.Filing开发者_运维技巧Date=( select date_sub(a2.FilingDate,Interval 10' at line 1

Can anyone tell me about the issue and its solution?

Your subquery can return more than one value per row in A1, MySQL doesn't know which value from A2 it should pick!

Me thinks your query could also read:

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

CAVEAT try this on a backup!