转换查询为使用EXISTS

Question

我写了一个查询来查找一些记录...

select account_num, mob_tele_feat_uid, mob_tele_feat, done
from LOAD_MOBILE_DATA lmd1
where (mob_tele_feat is null and mob_tele_feat_uid is not null)
   or (mob_tele_feat is not null and mob_tele_feat_uid is null);

表中数据--

4579214598412   223344  {sw_dcf=Gold}|{sw_dcf_active_voice_bsg=Silver}|{sw_cfb=bronze}|{sw_c=bronze}    S
4579214598412   223344                                                                                  S
     null       null    null                                                                            P

第一个查询的输出是

4579214598412   223344  null    S

我想在update语句中使用exists，如下所示

update load_mobile_data lmd
  set done = 'P'
where exists
  (select account_num, mob_tele_feat_uid, mob_tele_feat, done
   from LOAD_MOBILE_DATA lmd1
   where (mob_tele_feat is not null and mob_tele_feat_uid is null)
      or (mob_tele_feat is null and mob_tele_feat_uid is not null)
     and lmd.account_num = lmd1.account_num
     and lmd.mob_tele_feat = lmd.MOB_TELE_FEAT
     and lmd.MOB_TELE_FEAT_UID = lmd.MOB_TELE_FEAT_UID);

我想在LOAD_MOBILE_DATA中标记DONE column =P，其中mob_tele_feat为null但mob_tele_feat_uid不为null，或者mob_tele_feat_uid为null但mob_tele_feat不为null

Answer 1

你做得太过了。如果此查询返回正确的记录：

select account_num, mob_tele_feat_uid, mob_tele_feat, done
from LOAD_MOBILE_DATA lmd1
where (mob_tele_feat is null and mob_tele_feat_uid is not null)
   or (mob_tele_feat is not null and mob_tele_feat_uid is null);

然后简单地改变这一点：

select account_num, mob_tele_feat_uid, mob_tele_feat, done
from LOAD_MOBILE_DATA lmd1

要这样做：

update load_mobile_data
set done = 'P'

Answer 2

使用此CTE更新请根据您匹配的列修改联接条件

    WITH temptable AS
    (
        SELECT * FROM load_mobile_data WHERE 
        (mob_tele_feat is null and mob_tele_feat_uid is not null) 
        OR (mob_tele_feat is not null and mob_tele_feat_uid is null)
    )
    UPDATE a SET a.done='P' FROM load_mobile_data a JOIN temptable b 
    ON a.account_num=b.account_num

Answer 3

最简单的方法是...只是更新你的查询。

UPDATE (
    select *
    from LOAD_MOBILE_DATA lmd1
    where (mob_tele_feat is null and mob_tele_feat_uid is not null)
       or (mob_tele_feat is not null and mob_tele_feat_uid is null)
)
SET done = 'P'

如果您真的想使用EXISTS运算符，则需要使用唯一标识表中每一行的主键列：

UPDATE  LOAD_MOBILE_DATA lmd1
SET done = 'P'
WHERE EXISTS (
        select *
        from LOAD_MOBILE_DATA lmd2
        where 
           lmd1.id = lmd2.id
           AND
           (
             (mob_tele_feat is null and mob_tele_feat_uid is not null)
             or (mob_tele_feat is not null and mob_tele_feat_uid is null)
           )
)