sql - 更新比我预期的更多的行

debugcn 发表于 Dev

用户8957934

我正在尝试更新我的表的一部分。如果我执行 select 语句，我会发现 17 次出现，但是当我更新它时，它会更新 997 次出现。我只想更新 17 次。这是我的代码：

update proc_try k set detail = (
select jobs from 
       (
with
      a ( nameHost ) as (
           select b.nameHost
        from     definition a ,schema.nodes b
        where b.nameHost = a.idNode or b.nodeid=a.idNode 
        and nodetype not like 'R'
        group by b.nameHost
        having   sum(1 + lengthb(nameJob)) - 1 > 4000
      )      
      select   nameHost, 'TOOLONG' as jobs
      from  a  
      UNION ALL 
      select p.nameHost, listagg(p.nameJob,',') within group (order by p.nameJob) as jobs
        from 
                ( 
                select distinct b.nameJob, a.nameHost
                from definition b
                right join schema.nodes a
                on b.idNode in (a.nodeid,a.nameHost) and
                b.application not like '@NOTINCLUDE'
                where a.nameHost not in (select * from a) and nodetype not like 'R'
                --b.application not like '@NOTINCLUDE'
       ) p 
        group by p.nameHost) random 
         where k.nameHost=random.nameHost);

请问你能帮帮我吗？

威廉·罗伯逊

您通常可以将复杂的update转换为merge：

merge into proc_try k
using
   ( select jobs
     from   ( with a(namehost) as
                   ( select b.namehost
                     from   definition   a
                            join schema.nodes b
                                 on  b.namehost = a.idnode
                                 or  (b.nodeid = a.idnode and nodetype <> 'R')
                     group  by b.namehost
                     having sum(1 + lengthb(namejob)) - 1 > 4000 )
              select namehost
                   , 'TOOLONG' as jobs
              from   a
              union all
              select p.namehost
                   , listagg(p.namejob, ',') within group(order by p.namejob) as jobs
              from   ( select distinct
                              b.namejob, a.namehost
                       from   schema.nodes a
                              left join definition b
                                   on  b.idnode in (a.nodeid, a.namehost)
                                   and b.application not like '@NOTINCLUDE'
                       where  a.namehost not in (select * from a)
                       and    nodetype not like 'R'
                     ) p
              group  by p.namehost
            ) random
   ) new_jobs
on (k.namehost = new_jobs.namehost)
when matched then update set k.detail = new_jobs.jobs;

这是未经测试的，因为我没有您的表格或示例数据。

编辑：看起来我们可以简化一下，这样：

merge into proc_try k
using
   ( with overlength (namehost) as
          ( select n.namehost
            from   definition d
                   join schema.nodes n
                        on  n.namehost = d.idnode
                        or  (n.nodeid = d.idnode and nodetype <> 'R')
            group by n.namehost
            having sum(1 + lengthb(n.namejob)) - 1 > 4000 )
     select o.namehost, 'TOOLONG' as jobs
     from   overlength o
     union all
     select sd.namehost
          , listagg(sd.namejob, ',') within group(order by sd.namejob) as jobs
     from   ( select distinct d.namejob, n.namehost
              from   schema.nodes n
                     left join definition d
                          on  d.idnode in (n.nodeid, n.namehost)
                          and d.application not like '@NOTINCLUDE'
              where  n.namehost not in (select o.namehost from overlength o)
              and    n.nodetype not like 'R'
            ) sd
     group  by sd.namehost
   ) new_jobs
on (new_jobs.namehost = k.namehost)
when matched then update set k.detail = new_jobs.jobs;