• notice
  • Congratulations on the launch of the Sought Tech site

MySQL's You can't specify target table for update in FROM clause solution

This article mainly introduces the solution to the You can't specify target table for update in FROM clause error in mysql. Friends who need it can refer to the following

The You can't specify target table for update in FROM clause error in MySQL means that you cannot select some values in the same table first, and then update the table (in the same statement). For example the following sql:

Copy the code The code is as follows:


delete from tbl where id in 
(
        select max(id) from tbl a where EXISTS
        (
            select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1
        )
        group by tac
)

 

Just rewrite it as follows:

 

code show as below:


delete from tbl where id in 
(
    select a.id from 
    (
        select max(id) id from tbl a where EXISTS
        (
            select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1
        )
        group by tac
    ) a
)

 

That is to say, the selected result is selected again through the intermediate table, so as to avoid errors. Note that this problem only occurs in mysql , mssql and Oracle do not have this problem.

 

 

You can't specify target table for update in FROM clause Meaning: The data that cannot be queried in the same table as the update data of the same table.

E.g:

I want to query the balance of t_user_asset plus 50000 as the value of the update field f_cashAmount, which is not acceptable.

 

UPDATE t_user_asset SET f_cashAmount =   

    (  

     SELECT (ua.f_cashAmount+50000) cashAmount FROM t_user_asset ua WHERE ua.f_userId = 290  

  )  

WHERE f_userId = 290  

Just modify it to the following writing, which means changing the direction and putting a layer outside the select, so that the database thinks that you are not looking up the data of the same table as the updated data of the same table:

 

UPDATE t_user_asset SET f_cashAmount =   

(  

  SELECT ub.cashAmount FROM  

        (  

             SELECT (ua.f_cashAmount+50000) cashAmount FROM t_user_asset ua WHERE ua.f_userId = 290  

        ) ub  

)  

WHERE f_userId = 290  


The above question is only for mysql database

 


Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+