mysql子查询在更新查询中返回多个值错误

Question

我运行了以下查询并收到错误 You can't指定目标表以在 FROM 子句中进行更新

update xyz x 
set x.PID = (select c.PPID 
            from 
            table1 c where c.FFID 
            in (select b.FID 
                from table1 a 
                join xyz b on a.FFID = b.fID 
                and a.FFIRD=b.PID and b.flag='4' 
                group by b.FID 
                having count(1) =1)
               ),flag='N' 
where x.FID = (select b.FID 
               from table1 a join xyz b on
               a.FFID = b.FID and 
               a.FFIRD=b.PID and b.flag='4' 
               group by b.FID 
               having count(1) =1
              )

我引用了 stackoverflow 并更改了我的代码，如下所示。但现在我得到子查询返回超过 1 行。请帮忙

update xyz x 
set x.PID = (select abc.PPID 
            from (select c.PPID 
                  from 
                  table1 c 
                  where c.FFID 
                  in (select b.FID 
                      from 
                      table1 a join xyz b on a.FFID = b.fID 
                      and a.FFIRD=b.PID and b.flag='4' 
                      group by b.FID 
                      having count(1) =1
                      ) as abc
                      ),flag='N' 
where x.FID = (select xyz.FID 
               from (select b.FID 
                     from table1 a join xyz b on
                     a.FFID = b.FID and 
                     a.FFIRD=b.PID and b.flag='4' 
                     group by b.FID 
                     having count(1) =1)as xyz
               )

Answer 1

根据您的工作脚本，您可以创建一个 CTE 并将数据放入其中，然后根据该脚本或沿这些方向的变体进行更新:

 WITH YourCTE (PPID)
 AS
 (select c.PPID 
        from 
        table1 c where c.FFID 
        in (select b.FID 
            from table1 a 
            join xyz b on a.FFID = b.fID 
            and a.FFIRD=b.PID and b.flag='4' 
            group by b.FID 
            having count(1) =1)
           ),flag='N' 
 where x.FID = (select b.FID 
           from table1 a join xyz b on
           a.FFID = b.FID and 
           a.FFIRD=b.PID and b.flag='4' 
           group by b.FID 
           having count(1) =1
          )
 )
UPDATE xyz x 
SET x.PID = (SELECT PPID FROM YourCTE)