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.
LIMIT is allowed in single-table updates only, as explained in the documentation:
For the single-table syntax, [...] if the
ORDER BYclause is specified, the rows are updated in the order that is specified. TheLIMITclause places a limit on the number of rows that can be updated.For multiple-table syntax,
ORDER BYandLIMITcannot 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
Update sub1
Inner join (
//your select statement another table
//condition
//Now use limit
Limit 10
)
On
sub.data = table.date
set
