MySQL Forums
Forum List  »  Newbie

Re: UPDATE statement...can't use FROM?
Posted by: Peter Brawley
Date: December 03, 2021 08:50PM

As the manual page for Update says (https://dev.mysql.com/doc/refman/8.0/en/update.html), "You cannot update a table and select directly from the same table in a subquery. You can work around this by using a multi-table update ..."

... Or a CTE eg given table t(id,d,val) ...

with cte as ( 
  select id from t where year(d)=2021 
)
update t 
join cte using(id) 
set t.val=t.val*1.05;

Options: ReplyQuote


Subject
Written By
Posted
Re: UPDATE statement...can't use FROM?
December 03, 2021 08:50PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.