Delete rows from a parent table without affecting the condition table

Question

I have a main table and a secondary one. Through a query I connect the secondary with the primary on the left, to filter the results of the primary. PhpMyAdmin disables editing and deleting the results because it does not have a unique column.

SELECT 
main_table.Id,
main_table.CustomerId,
main_table.CustomerName,
main_table.CustomerMail,
main_table.TravelId,
secondary_table.Sell
FROM main_table
LEFT JOIN secondary_table ON secondary_table.TravelId = main_table.TravelId
WHERE main_table.TravelId < '2000'
AND secondary_table.Sell = '0'  
ORDER BY main_table.Id DESC;

I thought about using a delete query but I'm worried that it will affect the rows in the secondary table since I only want to delete the corresponding ones in the main table without affecting the secondary.

I would like to now:

1. How to fix the query or what I am doing wrong to make PhpMyAdmin throw that error, so I can delete the records manually from PhpMyAdmin.
2. On the other hand, to learn, I would like to know how I should perform a delete query so as not to delete any records from the secondary table, only from the main one but taking into account the filtering I do when connecting them and filtering by sell = '0' and TravelId < '2000'.

Answer 1

It sounds like you have a cascading delete on the foreign key, but you don't want that. Let's see SHOW CREATE TABLE to confirm that error.

Which rows do you want to delete? Which table do you want to delete them from? Is the SELECT that you provided fetching the rows you want to delete? I doubt it, since it fetches data from both tables.

It seems like you don't need LEFT.

Maybe this is what you want:

DELETE
    FROM  main_table
    JOIN  secondary_table  ON secondary_table.TravelId = main_table.TravelId
    WHERE  main_table.TravelId <  '2000'
      AND  secondary_table.Sell = '0' 

But read about multi-table DELETE in the manual.