CODEWITHSUNDEEP
mysql
Jack
Jack

Reputation: 163

DELETE rows from multiple tables with JOIN in Mysql

I know dozen of questions are available here but I have specific issue which I didn't resolve

I have two tables user_bookmarks and highlights.

user_bookmarks contains all the users bookmarks and highlights contains the selected text of particular bookmark.

suppose two users having same bookmarks and both did some highlight texts.

Now user_bookmarks and highlight tables contains two rows of same bokkmark_id.

if one user delete that bookmark then only one row will be deleted from both tables.

I did some query but didn't success.

here is my query

 DELETE FROM user_bookmarks,highlights USING user_bookmarks
 LEFT JOIN highlights ON user_bookmarks.bookmark_id =
 highlights.bookmark_id WHERE user_bookmarks.`user_id` = 39 
 AND user_bookmarks.`bookmark_id`= 1556

Upvotes: 0

Views: 70

Answers (1)

rpc1
rpc1

Reputation: 688

In my opinion , accourding to your logic (in comments), you need to join table using composit key user_id,bookmark_id

 DELETE user_bookmarks,highlights  FROM user_bookmarks
 LEFT JOIN highlights ON 
 user_bookmarks.bookmark_id =  highlights.bookmark_id and 
 user_bookmarks.user_id =  highlights.user_id 
WHERE user_bookmarks.user_id = 39 
 AND user_bookmarks.`bookmark_id`= 1556

It delets rows from all rows with user_id=39 and bookmark_id=1556 from both tables

to delete rows from multiple unit to put all tables into delete section

Upvotes: 1

Related Questions