How to modify and select the same table in MySQL

In MySQL, you cannot modify the same table and use the SELECT statement. You can use JOIN as an example.

The original statement using SELECT:

Update spms. user
Set DirectReport = (select DirectReport from spms. user where UserName = '% s ')
Where UserName in (select UserName from spms. user where DirectReport = '% s ')

Statements implemented using JOIN:

Update spms. user as u1
Inner join spms. user as u2 on u1.DirectReport = u2.UserName
Set u1.DirectReport = u2.DirectReport where u2.UserName = '% s'

Reference: SQL Update Syntax http://dev.mysql.com/doc/mysql/en/UPDATE.html