Clever Geek Handbook

MySQL - delete an item from two tables

I am trying to drop a table items

. And also from the table saves

, if it exists.

This is mistake:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'i LEFT JOIN saves s ON i.id = s.item_id 

DELETE FROM items i
LEFT JOIN saves s
ON i.id = s.item_id
WHERE i.id = ? AND s.item_id = ?
AND
NOT EXISTS (SELECT id FROM pending_wins WHERE item_id = ?)
AND
NOT EXISTS (SELECT id FROM bids WHERE item_id = ?)
+3


source to share


4 answers


If you want to delete from one table say items

try this:

DELETE i FROM items i
LEFT JOIN saves s
ON i.id = s.item_id
WHERE i.id = ? AND s.item_id = ?
AND
NOT EXISTS (SELECT id FROM pending_wins WHERE item_id = ?)
AND
NOT EXISTS (SELECT id FROM bids WHERE item_id = ?)


If you want to delete a record from both tables try this:

DELETE i, s FROM items i
LEFT JOIN saves s
ON i.id = s.item_id
WHERE i.id = ? AND s.item_id = ?
AND
NOT EXISTS (SELECT id FROM pending_wins WHERE item_id = ?)
AND
NOT EXISTS (SELECT id FROM bids WHERE item_id = ?)
0


source 


DELETE i, s 
FROM items i
LEFT JOIN saves s
ON i.id = s.item_id
WHERE s.item_id = ?
AND
 NOT EXISTS (SELECT id FROM pending_wins WHERE item_id = ?)
AND
 NOT EXISTS (SELECT id FROM bids WHERE item_id = ?)


0


source


Use the following query to remove data from table i:

DELETE i FROM items i
LEFT JOIN saves s
ON i.id = s.item_id
WHERE i.id = ? AND s.item_id = ?
AND
NOT EXISTS (SELECT id FROM pending_wins WHERE item_id = ?)
AND
NOT EXISTS (SELECT id FROM bids WHERE item_id = ?)
0


source


You just need to add i

before FROM

to your request

DELETE i FROM items i
LEFT JOIN saves s
ON i.id = s.item_id
WHERE i.id = ? AND s.item_id = ?
AND
NOT EXISTS (SELECT id FROM pending_wins WHERE item_id = ?)
AND
NOT EXISTS (SELECT id FROM bids WHERE item_id = ?)
0


source






More articles:


All Articles