0

I want to have a relational data between two table with DELETE query. I don't know what keyword should I search and such confusing for me to learn relational database.

So in this case, I want to delete one of the user data in the user table. So the buy_product table which contains the user_id column will also be deleted along with the rows in it.

This is user table:

user_id name
1 John
2 Doe

This is buy_product table:

id_product name user_id
1 Cookies 2
2 Pizza 2
3 Burger 1

For example, if I run the DELETE FROM user WHERE user_id = 2 query, the result is:

user_id name
1 John

And buy_product table with user_id = 2 also deleted the data that I want without run DELETE FROM buy_product WHERE user_id = 2 query like this:

id_product name user_id
3 Burger 1

I think for all understandable answers means a lot to me. Thanks!

Noobie Cooder
  • 141
  • 1
  • 1
  • 7
  • Does this answer your question? [How to delete from multiple tables in MySQL?](https://stackoverflow.com/questions/3331992/how-to-delete-from-multiple-tables-in-mysql) – kmoser Feb 16 '22 at 03:17

2 Answers2

0

Ideally you should configure cascading deletion such that when a record is deleted from the user table all dependent records are also deleted from the buy_product table. In lieu of that, we can try doing a delete join here:

DELETE u, bp
FROM user u
INNER JOIN buy_product bp
    ON bp.user_id = u.user_id
WHERE u.user_id = 2;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

If you defined a foreign key constraint with ON DELETE CASCADE between buy_product and user then deleting a row from the parent table user will automatically delete the related rows in the child table buy_product.

For example, buy_product could be created as:

create table buy_product (
  id_product int,
  name varchar(50),
  user_id int references user (user_id) on delete cascade
);
The Impaler
  • 45,731
  • 9
  • 39
  • 76