곰곰히 생각해보면

Mysql에서 Oracle을 생각하고 UPDATE를 진행하다 발생한 오류이다;
You can't specify target table '테이블명' for update in FROM clause

원인은 아래와 같이 같은 테이블을 참조. 즉, Where에 같은 테이블을 참조하면 발생되는 에러이다.

UPDATE 테이블명1 BB
SET BB.PASSWORD = (select AA.password from 테이블명1 AA WHERE AA.user_id = 'rosebay'), 
      BB.UPDATED_BY = 'rosebay', BB.UPDATE_DATE = now()
WHERE BB.user_id = 'wildflower'   ;

해결방법은 subquery 절을 한번 더 감싼 후(임시테이블로 처리) UPDATE 한다.