CODEWITHSUNDEEP
mysqlinner-join
tonymx227
tonymx227

Reputation: 5451

Issue with MySQL delete and inner join

I need to create a query to delete cfc_registration that have an old cfc_tournament.

My query :

delete from cfc_registration
inner join cfc_tournament on cfc_tournament.id = cfc_registration.cfcTournamendId
where cfc_registration.cfcTournamentId = cfc_tournament.id and cfc_tournament.createdAt >= DATE_SUB(NOW(),INTERVAL 1 YEAR);

Issue :

#1064 - 'inner join cfc_tournament on cfc_tournament.id = cfc_registration.cfcTournamendI' à la line 2

Upvotes: 0

Views: 25

Answers (2)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30565

I personally prefer exists statement rather than inner join approach

delete from cfc_registration
where exists (
  select 1 from cfc_tournament 
  where cfc_tournament.id = cfc_registration.cfcTournamendId
    and cfc_tournament.createdAt >= DATE_SUB(NOW(),INTERVAL 1 YEAR)
);

BTW: for inner join approach, you dont need to use cfc_registration.cfcTournamentId = cfc_tournament.id and both on inner join and where. one is enough.

delete cfc_registration 
from cfc_registration
inner join cfc_tournament 
  on cfc_tournament.id = cfc_registration.cfcTournamendId
where cfc_tournament.createdAt >= DATE_SUB(NOW(),INTERVAL 1 YEAR);

Upvotes: 1

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

The issue is with your DELETE FROM at start. You should use DELETE t1 FROM table t1 since you are specifying what you want to delete. This is implemented this way in MySQL, because you could delete records from both tables at once.

I believe this should work:

DELETE t1
FROM cfc_registration t1
  INNER JOIN cfc_tournament t2
    ON t2.id = t1.cfctournamendid
WHERE
  t1.cfctournamentid = t2.id
  AND t2.createdat >= date_sub( now(), interval 1 YEAR );

Upvotes: 2

Related Questions