0

I have the following query where I want to limit the number of rows it updates for the subs table. It keeps hitting an error though, where am I going wrong?

UPDATE subs t1
        INNER JOIN imp_subscriptionlog t2 
             ON t1.subscription_id = t2.subscription_id
SET t1.cancellation_date = t2.date

WHERE t2.event = 'subscription_cancelled'
LIMIT 35

This is the error:

Incorrect usage of UPDATE and LIMIT

Error code 1221.

GMB
  • 216,147
  • 25
  • 84
  • 135
Rob
  • 6,304
  • 24
  • 83
  • 189
  • *It keeps hitting an error*: please share the entire error message. – GMB Aug 04 '20 at 09:51
  • ___It keeps hitting an error___ Mind sharing that with us, it might be helpful? – RiggsFolly Aug 04 '20 at 09:51
  • 2
    1) LIMIT without ORDER BY is lottery. 2) Use correlated subquery for new value. ORDER BY / LIMIT cannot be used in [multiple-table UPDATE](https://dev.mysql.com/doc/refman/8.0/en/update.html). – Akina Aug 04 '20 at 09:52
  • 1
    Does this answer your question? [MySQL - UPDATE query with LIMIT](https://stackoverflow.com/questions/6289729/mysql-update-query-with-limit) – Cid Aug 04 '20 at 09:54
  • The question is incomplete. Based on the names of the table, I would expect the second table to have multiple matches for each subscription. – Gordon Linoff Aug 04 '20 at 10:47

2 Answers2

2

LIMIT is allowed in single-table updates only, as explained in the documentation:

For the single-table syntax, [...] if the ORDER BY clause is specified, the rows are updated in the order that is specified. The LIMIT clause places a limit on the number of rows that can be updated.

For multiple-table syntax, ORDER BY and LIMIT cannot be used.

You can rewrite the query to use a correlated subquery instead of a join:

update subs
set cancellation_date = (
    select t2.date
    from imp_subscriptionlog t2 
    where t2.subscription_id = subs.subscription_id and t2.event = 'subscription_cancelled'
)
order by ???
limit 35

Notes:

  • you should be specifying an order by clause in the query, otherwise it is undefined which rows will actually be updated

  • the query implicitly assumes that there is always just one matching row in imp_subscriptionlog for each row in subs; if that's not the case, then you must order by and limit 1 in the subquery as well, or use aggregation

  • we can also ensure that there is a match before updating by adding a where clause to the query

Here is a "safer" version of the query, that updates to the maximum date value available in the other table, while not modifying rows that have no match:

update subs
set cancellation_date = (
    select max(t2.date)
    from imp_subscriptionlog t2 
    where t2.subscription_id = subs.subscription_id and t2.event = 'subscription_cancelled'
)
where exists (
    select 1
    from imp_subscriptionlog t2 
    where t2.subscription_id = subs.subscription_id and t2.event = 'subscription_cancelled'
)
order by ???
limit 35
GMB
  • 216,147
  • 25
  • 84
  • 135
  • @Akina: yes, that would be safer, depending on OP's actual data. I updated my answer. – GMB Aug 04 '20 at 10:02
  • @GMB I've set the limit to 10 but keeping getting the error "Lock wait timeout exceeded; try restarting transaction Error code 1205.". Is there any way around that? – Rob Aug 04 '20 at 10:03
  • @Rob: it looks like there is a concurrent process currently updating the table. – GMB Aug 04 '20 at 10:06
0

Update sub1

Inner join (

      //your select statement another table
      

      //condition

      //Now use limit
       Limit 10
      )
     On 
      sub.data = table.date
      set
Razor B
  • 1
  • 1