I want to delete multiple rows from different tables using C# and mysql. I have 3 tables users, items and edata. In the users tables I have these columns: ID(primary key), username, password, rights. In the edata I have: ID(primary key), name, surname, age, city, and in the items tables: ID(primary key), name, cost, seller_id, bids. The query that I've managed to write is this:
delete database.users, database.edata,database.items
from database.users
left join database.edata ON database.users.id = database.edata.id
left join database.items on database.users.id = database.items.seller_id
where database.users.username = 'mark'
The query deletes all rows where the id of the user in the user table is the same with the id of the user in edata table and the seller_id in the items table and the username from the user table is equal to a given value, in this case 'mark'. The query works just fine in mysql workbench, but for some reason when I want to use it in visual studio in my C# code I get this this exception: Unknown table in multi delete. I red a lot on this subject and i found out that there are some bugs with multi deletion if aliases are being used, but in my case I don't have any aliases used. I am even using the full names of the tables and the table columns. Right now I'm stuck with having to use few different queries to do the deletion of the rows and honestly I don't like it, when there can be used just one query. So is there anyone who can help me out? Any feedback will be appreciated.
You should use your table alias in the top DELETE part, not the full name of the table.
Can you try this?
DELETE u
FROM database.users AS u
LEFT JOIN database.edata AS d
ON d.id = u.id
LEFT JOIN database.items AS i
ON i.seller_id = u.id
WHERE u.username = 'mark'
Just wrote this at work so I can't test it. It's possible that you need to mention the other aliases between the DELETE and the FROM.