[Solved] sql Error: You can’t specify target table ‘tb_cr_circulate_gtsc’ for update in FROM clause

Mysql database is used

The SQL statement is as follows

 1 UPDATE tb_cr_circulate_gtsc 
 2 set path_status='A06'
 3 where
 4 order_id='Ph800601211123155507'
 5 and
 6 receive_role='21075116-001'
 7 and
 8 receive_role_name='024722cd-b197-462f-bdc2-f0423d88e580&Z&11caea25-4996-4682-aa3c-ea698e5140cc&21075116-001&四川省川东农药化工有限公司'
 9 and 
10 ((select COUNT(path_status)   FROM tb_cr_circulate_gtsc  where order_id='Ph800601211123155507' and path_status='A04' and is_current_point='1' )
< (select COUNT(path_status) FROM tb_cr_circulate_gtsc where order_id='Ph800601211123155507' and is_current_point='1') )

According to the online query: you cannot select some values of the same table in the same SQL statement, and then update the table.

Solution: select multiple times through an intermediate table

1 ( 
2 (SELECT statusSum from   (select COUNT(path_status)  as statusSum FROM tb_cr_circulate_gtsc  where order_id='Ph800601211123155507' and path_status='A04' and is_current_point='1' ) )
3 <  
4 (SELECT statusSum from  (select COUNT(path_status)  as statusSum  FROM tb_cr_circulate_gtsc where order_id='Ph800601211123155507' and is_current_point='1') )
5 )

Similar Posts: