CODEWITHSUNDEEP
mysqlsqlsql-updatesql-order-byinner-join
Rob
Rob

Reputation: 6370

Using `limit` with an update sql query

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.

Upvotes: 1

Views: 3367

Answers (2)

Razor B
Razor B

Reputation: 1

Update sub1

Inner join (

      //your select statement another table
      

      //condition

      //Now use limit
       Limit 10
      )
     On 
      sub.data = table.date
      set

Upvotes: 0

GMB
GMB

Reputation: 222432

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

Upvotes: 2

Related Questions