how do I update the date type column?

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

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.FilingDate=( select date_sub(a2.FilingDate,Interval 10' at line 1

Can anyone tell me about the issue and its solution?

Answer 1

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!