[MySQL]Updating only rows returned by another query...

[LazyManc]

I need to update a bunch of rows in a transaction table to status 'ac'.

The rows I need to update are given by the query:

SELECT transaction.id
FROM transaction
INNER JOIN transaction_item ON transaction_item.transaction_id = transaction.id
INNER JOIN product ON product.id = transaction_item.product_id
WHERE product.is_trial =1
AND transaction_item.status = 'ac'
AND transaction.status = 'pd'

I've tried something like

UPDATE transaction SET status = 'ac' WHERE transaction.id IN (
SELECT transaction.id
FROM transaction
INNER JOIN transaction_item ON transaction_item.transaction_id = transaction.id
INNER JOIN product ON product.id = transaction_item.product_id
WHERE product.is_trial =1
AND transaction_item.status = 'ac'
AND transaction.status = 'pd'
)

but I get an error:

#1093 - You can't specify target table 'transaction' for update in FROM clause

I know it can be done, but I'm not sure of the correct syntax..

 

[LazyManc]

In the end I just performed the SELECT query and outputted to csv, then cut and pasted the id's into an UPDATE query.

Would still like to know if it can be done with one query - could be useful in future.