I have 2 tables users and pd having a common column i_uid
For deleting from both the tables a single row having i_uid of 1010 I issued the following statement using mysqli::query in php script
DELETE
FROM `users`,`pd`
WHERE `users`.`i_uid`=`pd`.`i_uid` AND `users`.`i_uid` = 1010;
Is there anything wrong?
since you are deleting rows from multiple table, the only missing is to specify on what tables the row will be deleted,
DELETE `users`,`pd`
FROM `users`, `pd`
WHERE `users`.`i_uid`=`pd`.`i_uid` AND `users`.`i_uid` = 1010;
in the query above, it will delete rows from both tables. I suggest to change statement using ANSI join like below,
DELETE a, b
FROM users a
INNER JOIN pd b
ON a.i_uid = b.i_uid
WHERE a.i_uid = 1010
if you want to delete from users table,
DELETE a
FROM users a
INNER JOIN pd b
ON a.i_uid = b.i_uid
WHERE a.i_uid = 1010
FROM clause are where the records will be deleted. the table after the FROM clause are where the records will be search. but it is optional if you are deleting in one table only
delete p, pc, l, c from posts p inner join post_content pc on p.post_id=pc.post_id inner join likes l on p.post_id=l.post_id inner join comments c on c.post_id=p.post_id where p.post_id=406;Use Left Join and specify the tables on which corresponding rows will be deleted.
DELETE users, pd
FROM users, pd
LEFT JOIN pd
ON pd.i_uid = users.i_uid
WHERE users.i_uid = 1010
Other answers are correct but since no one mentioned it I will. You could use InnoDB engine and specify foreign keys and DELETE ON CASCADE. So that each time you delete a user it will delete all referencing rows from different tables.
Read up on Foreign Keys and DELETE ON CASCADE
To keep it simple I suggest using just two queries:
DELETE FROM `users` WHERE `i_uid` = 1010;
DELETE FROM `pd` WHERE `i_uid` = 1010;
