strange delete syntax with join for mysql
With MySQL 5, there are two rules if you need to join to another table in order to delete records from somewhere.
- Rule 1: no aliases allowed. Refer only to tables as themselves – better hope the names aren’t very long!
- Rule 2: specify table name from which to delete before the FROM clause.
Like so:
DELETE sales from sales
JOIN retailers on sales.retailer_id = retailers.id
WHERE retailers.merchant in ('Test Merchant',
'Other merchants that should not be here')
