DELETE rows from multiple tables with JOIN in Mysql

Question

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

user a's bookmark on page 12 of book 1 is NOT the same as user b's bookmark on page 12 of book 1. they SHOULD be able to delete them independently. — pala_
– pala_, Commented Apr 14, 2015 at 5:06
What is you question? Do you want delete only one row from each table, or delete all rows for this bookmark? What's mean field bookmark_id ? is it a page number? May be is better way to do a unique bookmark instead of composite key user_id+bookmark_id — rpc1
– rpc1, Commented Apr 14, 2015 at 5:40
I want to delete all rows from user_bookmarks and single row from highlights table. bookmark_id tends to single article and multiple user can have same article, I also given condition for selected user — Jack
– Jack, Commented Apr 14, 2015 at 5:47

rpc1 · Accepted Answer · 2015-04-14 07:01:54Z

1

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

edited Apr 14, 2015 at 7:01

answered Apr 14, 2015 at 6:43

rpc1

68811 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rpc1 Over a year ago

sorry, I change syntax... I don't have mysql at home therefore can't check syntax

Collectives™ on Stack Overflow

DELETE rows from multiple tables with JOIN in Mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related