Delete query only deletes one record

Hi I’ve got the folowing query:

DELETE tickets, archive, files
FROM tickets
LEFT JOIN ticket_files
ON ticket_files.ticket_id = tickets.ticket_id
LEFT JOIN archive
ON archive.document_id = ticket_files.document_id
LEFT JOIN files
ON files.file_id = ticket_files.file_id
WHERE tickets.ticket_id = 21

When the ticket doesn’t have any record in the join table ticket_files then the ticket is deleted correctly but if there is more than one record in the ticket_files then only one record is deleted from the tables archive and files. How can i make sure all the joined records are deleted also from archive and files? many thanks

It would help to know which database you’re using. Different engines work differently and have different constrictions.
One of those constrictions could be that you can’t leave an orphan record and cascade works differently.
Not that I can help you much. I haven’t tried deleting records from various tables at the same time like you’re trying to do.
But I do know that sometimes the differences between databases are important.

I have seen the type of query you’re trying to do with left joins and with inner joins.

You may want to try to use an inner join and see what happens, or switch the order of the relationships. With some databases, that helps.

Hi @molona thanks for your answer I’m using mysql database. I’ve also tried with INNER JOIN but I get same result.

I would have to read the documentation. I am assuming that you’re using InnoDB and if I remember well (and don’t trust me because my memory is really crap) InnoDB would need a trasaction, meaning that you would have to do something like

START TRANSACTION;;

DELETE
FROM    tableA
WHERE   id= 1

DELETE
FROM    tableB
WHERE   id= 1;
...
COMMIT;

I also confess that it is too early in the morning for me to think logically and maybe when I come back to this thread in a few hours I will see the problem right away :laughing:

In principle, the query looks OK for me. I suggested the chang in the other of the left joins because, funny enough, it happened to me in the past. Just by changing the order everything started to work.

It could be something as simply as instead of writing left join on a.id=b.id, writing left join on b.id=a.id.

i don’t know the answer to Vince’s problem but i do know that this ain’t it

the way equality works, if x = y, you can be damned sure that y = x

I agree with you but I can assure that it happened to me a couple of times that just by changing the order, the query worked.
Does it make any sense? Not at all. But it happened.

Hi I’ve tried changing the order and unfortunately still doesn’t work. I believe I can fix this with two queries. I can check if there are any files associated to a ti check first with a select query and then use two different delete queries according to the select result. I was just hoping I could do it with one query only also avoiding the use of transactions in mysql

To be honest, wouldn’t it be better to work with foreign keys here?

I am not sure, but it sounds that DELETE CASCADE will be an option.

1 Like

Hi @Thallius the table tickets_files does have three columns ticket_id file_id and document_id and they are linked with foreign keys with the tables tickets, files and archive

That is why I don’t understand why it only deletes one record from table files and archive if I’ve got for example two records related to same ticket inside ticket_files

Hi @sibertius the relations inside ticket_files table are all set to on delete cascade

Can you please show us your db table structures including FK’s.

Hi @vincekaribusana ,

The problem is you have set the foreign keys for the tables files and archive in the wrong place. (ticket_files)

Foreign Keys belong in the child tables. Tables files and archive are the children of ticket files so each one of those tables needs a foreign cascading key in each one of those tables.

So, to fix, delete the two keys from ticket_files for those tables and then add a key to each of the tables files and archive. The other key to table tickets is OK as is.

After that, all you have to do is delete from the tickets table and the deletes will cascade down through the other 3 tables.

This is all the query you will need to delete a record from all the tables.


DELETE FROM tickets WHERE ticket_id = xxx

FYI: The order of your data inserts matter. First tickets, then ticket_files, then either of archive and/or files.

Hi @benanamen many thanks for your help.
I thought I could use the archive and files tabels (which I now thinking I can merge into one table) as general tables not only for all files associated to tickets but also for files associated to other tables in the database (for example I’ve got a table communications) and this is the reason why I tried to use a junction table (many-to-many) rather than add a foreign key into archive or files tables (as the foreign key should then refer to table communication and tickets not sure if is possible).

Do you suggest to use different file and archive tables for communications and tickets? Also from what you said than do I still need the ticket_files table? Sorry maybe is me misunderstanding :frowning:

The solution is to the problem “How can i make sure all the joined records are deleted also from archive and files?” with the tables you are currently using.

I have no idea what you have going on so I am not able to advise on a proper schema. That would also be discussion for a new thread. You would need to provide a high level overview of what you are doing and the problems you want to solve if you would like our advice on how to design your database.

Hi @benanamen thanks for your reply. I think for now I’ll use two queries: first one to check if the ticket_id has got values in the ticket_files table and then if there are values use the following query:


DELETE archive, files
                    FROM ticket_files
               LEFT JOIN archive
                      ON ticket_files.document_id = archive.document_id
               LEFT JOIN files
                      ON files.file_id = ticket_files.file_id
                   WHERE ticket_files.document_id = :ticketD

Otherwise just delete the ticket with the following query

DELETE FROM tickets WHERE ticket_id = :ticketD

Because the first query obviously doesn’t work if the ticket doesn’t have files associated to it. I was hoping I could do it with one query but it is not possibile with the database structure I’ve got. Many thanks

Not true. There is no need for query gymnastics.

You have shown two different query results.

Are you wanting to keep the ticket and delete any associated data or do you just want to delete the associated data?

Either way, you only need one query if you make the changes I told you to do.

For deleting all the data you would do this:

DELETE FROM tickets WHERE ticket_id = :ticketD

For deleting just the archive and files you would do this:

DELETE FROM ticket_files WHERE ticket_id = :ticketD

Hi @benanamen i want to delete the ticket and if there are files associated then also delete the files. I’ve created a new table ticket_archive where I put a fk referring to tickets’ ID and on delete cascade the relation now is one to many and not many to many (my mistake) in this way deleting the ticket does cascade to files as well. Many thanks for your suggestions and help as always :wink:

1 Like