mysql - 在MySQL中使用内连接和having更新表

Question

我在 MySQL 中有这个选择查询:

select r.list_id
from vicidial_list as r
inner join vicidial_log as p
on r.phone_number=p.phone_number
where p.user='vdad' and p.list_id='30000' and p.status='na' and r.alt_phone is not null and r.alt_phone!=' ' and r.alt_phone!='' and r.phone_number is not null and r.phone_number!='' and r.phone_number!=' '
and r.alt_phone not in(
select k.phone_number from vicidial_list as k)
group by p.phone_number
having count(p.phone_number)>10

我想更新所有r.list_id。我能怎么做？如果我写这个我会收到错误:

update vicidial_list
set vicidial_list.list_id='12345'
where vicidial_list.list_id in
(
   select r.list_id
    from vicidial_list as r
    inner join vicidial_log as p
    on r.phone_number=p.phone_number
    where p.user='vdad' and p.list_id='30000' and p.status='na' and r.alt_phone is not null and r.alt_phone!=' ' and r.alt_phone!='' and r.phone_number is not null and r.phone_number!='' and r.phone_number!=' '
    and r.alt_phone not in(
    select k.phone_number from vicidial_list as k)
    group by p.phone_number
    having count(p.phone_number)>10
)

错误是:#1093 - 您无法在 FROM 子句中指定要更新的目标表“vicidial_list”

Answer 1

更新时会检查每一行的 WHERE 子句。由于前一行可能已更改，并且这可能会影响 WHERE 子句中子查询的结果，因此这是不允许的。将子查询放在 FROM 子句中，然后加入其中。

update vicidial_list
join 
(
   select r.list_id
    from vicidial_list as r
    inner join vicidial_log as p
    on r.phone_number=p.phone_number
    where p.user='vdad' and p.list_id='30000' and p.status='na' and r.alt_phone is not null and r.alt_phone!=' ' and r.alt_phone!='' and r.phone_number is not null and r.phone_number!='' and r.phone_number!=' '
    and r.alt_phone not in(
    select k.phone_number from vicidial_list as k)
    group by p.phone_number
    having count(p.phone_number)>10
) sq ON sq.list_id = vicidial_list.list_id
set vicidial_list.list_id = '12345';