CODEWITHSUNDEEP
mysqlsqlsql-updatesubqueryinner-join
superhero
superhero

Reputation: 6482

Update multiple rows in MySql from result of a sub-query

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')

Why does the above query work, when the blow queries does not?

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`))

ERROR 1093 (HY000): You can't specify target table 'event' for update in FROM clause

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              |                |
+-----------+---------------------+------+-----+-------------------+----------------+

Upvotes: 0

Views: 35

Answers (1)

GMB
GMB

Reputation: 222432

MySQL does not support re-using the updated table in a subquery. You could use a join instead:

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'

Upvotes: 2

Related Questions