SQL Delete with join 4

Question

I tried this request :

DELETE FROM user_tools AS UT
LEFT OUTER JOIN tools AS T ON UT.tool_id = T.id
WHERE UT.user_id = :user_id AND T.category = :category";

My goal is to delete records from a table with a condition from a join in an other one. I try several solution without success. Can you help ?

(I use PDO, it's why i have : in my where clause, but it's the same with values.

Thanks

Answer 1

You need to specify from which tables to delete in a join. You do that right after the keyword delete

DELETE UT
FROM user_tools AS UT
LEFT OUTER JOIN tools AS T ON UT.tool_id = T.id
WHERE UT.user_id = :user_id 
AND T.category = :category";

By the way your condition T.category = :category turns your left join into an inner join.