mysql You can't specify target

作者: Rinaloving | 来源:发表于2023-04-09 10:49 被阅读0次

错误提示 You can't specify target table 't1' for update in FROM clause

它的意思是说，不能在同一语句中，先select出同一表中的某些值，再update这个表，即不能依据某字段值做判断再来更新某字段的值。

解决

select 的结果再通过一个中间表 select 多一次，就可以避免这个错误

UPDATE mz_villagedb t1 JOIN mz_person t2 
ON t1.F_Name = t2.F_Id  
SET t1.F_EnsurePopulation = t2.F_ItemName,
    T1.F_TypeCoverage = t2.F_ItemName,
    t1.F_HomeAddress = t2.F_ResidentialAdd,
    t1.F_Number = t2.F_ContactWay,
    t1.F_YearSumInsured = t2.F_ProviteMoney+0,
    t1.F_Account = t2.F_BankCardNum,
    t1.F_MemberName1 = t2.F_MemberName
WHERE t1.F_Name = 100 AND  t1.F_FTime =  (SELECT t3.* FROM (SELECT F_FTime FROM  mz_villagedb  WHERE F_Name = 100 ORDER BY F_FTime DESC LIMIT 0,1) t3)