在mysql中更新别名(Update with alias in mysql)

我正在尝试更新记录使用别名，但它给了我错误。

SQL查询如下：

UPDATE file_associations as fa
SET fa.scheduled_charge_id = NULL 
WHERE fa.faid IN(
  SELECT faid
  FROM file_associations as fa
  JOIN maintenance_requests mr ON ( mr.cid = fa.cid
    AND mr.id = fa.maintenance_request_id ) 
  JOIN scheduled_charges sc ON ( sc.cid = fa.cid
    AND sc.scid = fa.scheduled_charge_id ) 
  WHERE fa.cid =235
    AND fa.scheduled_charge_id IS NOT NULL 
    AND fa.maintenance_request_id IS NOT NULL 
    AND fa.faid IN ( 1, 7 )
);
 
 错误是： 
 
 
 
 ＃1093 - 您无法在FROM子句中为更新指定目标表'file_associations' 
 
 
 任何人都可以帮助我更新记录使用别名。 
I am trying to update the record using alias but it gives me error.
 
The SQL query is the following:
 
UPDATE file_associations as fa
SET fa.scheduled_charge_id = NULL 
WHERE fa.faid IN(
  SELECT faid
  FROM file_associations as fa
  JOIN maintenance_requests mr ON ( mr.cid = fa.cid
    AND mr.id = fa.maintenance_request_id ) 
  JOIN scheduled_charges sc ON ( sc.cid = fa.cid
    AND sc.scid = fa.scheduled_charge_id ) 
  WHERE fa.cid =235
    AND fa.scheduled_charge_id IS NOT NULL 
    AND fa.maintenance_request_id IS NOT NULL 
    AND fa.faid IN ( 1, 7 )
);
 
Error is:
 
 
 
#1093 - You can't specify target table 'file_associations' for update in FROM clause
 
 
Can anybody please help me to update record using alias.
                
最满意答案
                
                    
                        
 不要where in select中使用where in select而是使用join 
 
update 
  (select faid
   from 
     file_associations as fa
     join maintenance_requests mr on ( mr.cid = fa.cid and mr.id = fa.maintenance_request_id ) 
     join scheduled_charges sc on ( sc.cid = fa.cid and sc.scid = fa.scheduled_charge_id ) 
   where
     fa.cid =235
     and fa.scheduled_charge_id is not null 
     and fa.maintenance_request_id is not null 
     and fa.faid in ( 1, 7 )
  ) s
  join file_associations as fa using (faid)
set fa.scheduled_charge_id = null 
don't use a where in select, use a join instead
 
update 
  (select faid
   from 
     file_associations as fa
     join maintenance_requests mr on ( mr.cid = fa.cid and mr.id = fa.maintenance_request_id ) 
     join scheduled_charges sc on ( sc.cid = fa.cid and sc.scid = fa.scheduled_charge_id ) 
   where
     fa.cid =235
     and fa.scheduled_charge_id is not null 
     and fa.maintenance_request_id is not null 
     and fa.faid in ( 1, 7 )
  ) s
  join file_associations as fa using (faid)
set fa.scheduled_charge_id = null