You can't specify target table 'b' for update in FROM clause

Posted by elo on Stack Overflow See other posts from Stack Overflow or by elo
Published on 2012-06-13T04:37:06Z Indexed on 2012/06/13 4:39 UTC
Read the original article Hit count: 144

Filed under:

I need to know where did i do wrong in this sql statement. I try to find solution in previous threads with similar problem but none can solve my problem. so i think maybe my statement is actually wrong.

update table1 b

left join table2 m on b.ICNO=m.ICNO

set b.SalMoveMth = '01'

where
    m.Status!='6' and 
    (DATE_FORMAT(startDateSand,'%m')='10' or DATE_FORMAT(startDateSand,'%m')='11' or 
        DATE_FORMAT(startDateSand,'%m')='12') and 
    ((select SalMoveMth from table1 where ICNO=table2.ICNO order by SalMoveMthStDt desc limit 1)!='10').

Thank You.

© Stack Overflow or respective owner

Related posts about mysql