从子查询的结果中更新MySql中的多行

Question

update `event` set 
  `name`   = concat(`name`,   ' [soft deleted]'), 
  `domain` = concat(`domain`, ' [soft deleted]') 
where `name` regexp 'portability reference|created new portability|new portability created|sent postpaid portability initiated sms|init new postpaid portability process verification sms|created new postpaid portability in masmovil|created new prepaid portability in masmovil'
  and `pid` in ('801413','794854')

当blow查询不工作时，为什么要执行上述查询？

update `event` set 
  `name`   = concat(`name`,   ' [soft deleted]'), 
  `domain` = concat(`domain`, ' [soft deleted]') 
where `name` regexp 'portability reference|created new portability|new portability created|sent postpaid portability initiated sms|init new postpaid portability process verification sms|created new postpaid portability in masmovil|created new prepaid portability in masmovil'
  and `pid`  in (select distinct `pid` from `event` where (`name` = 'created new postpaid portability in masmovil' or `name` = 'created new prepaid portability in masmovil') and not JSON_LENGTH(`data`))

update `event` set 
  `name`   = concat(`name`,   ' [soft deleted]'), 
  `domain` = concat(`domain`, ' [soft deleted]') 
where `name` regexp 'portability reference|created new portability|new portability created|sent postpaid portability initiated sms|init new postpaid portability process verification sms|created new postpaid portability in masmovil|created new prepaid portability in masmovil'
  and `pid` = ANY (select distinct `pid` from `event` where (`name` = 'created new postpaid portability in masmovil' or `name` = 'created new prepaid portability in masmovil') and not JSON_LENGTH(`data`))

错误1093 (HY000)：不能指定目标表“事件”以便在FROM子句中进行更新

mysql> describe event;
+-----------+---------------------+------+-----+-------------------+----------------+
| Field     | Type                | Null | Key | Default           | Extra          |
+-----------+---------------------+------+-----+-------------------+----------------+
| id        | bigint(20) unsigned | NO   | PRI | NULL              | auto_increment |
| timestamp | timestamp           | NO   | MUL | CURRENT_TIMESTAMP |                |
| pid       | varchar(30)         | NO   | MUL | NULL              |                |
| domain    | varchar(50)         | NO   | MUL | NULL              |                |
| name      | varchar(200)        | NO   | MUL | NULL              |                |
| data      | json                | YES  |     | NULL              |                |
+-----------+---------------------+------+-----+-------------------+----------------+

Answer 1

MySQL不支持在子查询中重用更新的表。您可以使用一个join代替：

update `event` e
inner join (
    select distinct `pid` 
    from `event` 
    where 
        (`name` = 'created new postpaid portability in masmovil' or `name` = 'created new prepaid portability in masmovil') 
        and not JSON_LENGTH(`data`)
) x on x.pid = e..pid
set 
     `name`   = concat(`name`,   ' [soft deleted]'), 
     `domain` = concat(`domain`, ' [soft deleted]') 
where `name` regexp 'portability reference|created new portability|new portability created|sent postpaid portability initiated sms|init new postpaid portability process verification sms|created new postpaid portability in masmovil|created new prepaid portability in masmovil'