Delete from multiple tables in sql

Question

Hello can someone help me here.

I would like to delete from multiple tables.

I want to delete id_grupo = 6 from grupos. But when I delete it I would like to delete from gerir_posts where id_grupo = 6. But from posts, id_post = 4, that's the same id from gerir_posts id_post; I want delete it too.

So what i realy want is when i remove a grup , the posts that are in that grup most be deleted too.

Picture Tables

This is a example of my db.

   CREATE TABLE Utilizadores
(
    id_utilizador int auto_increment not null primary key,
    Nome varchar(50)
);

INSERT INTO utilizadores VALUES (1,'Admin');

CREATE TABLE Grupos
(
    id_grupo int auto_increment not null primary key,
    Nome_grupo varchar(50)
);
INSERT INTO grupos VALUES (1,'Grupo');

CREATE TABLE Gerir_grupos
(
    id_gerir_grupo int auto_increment not null primary key,
    id_grupo int,
    id_utilizador int,
    FOREIGN KEY (id_utilizador) references Utilizadores(id_utilizador),
    FOREIGN KEY (id_grupo) references Grupos(id_grupo) on delete cascade
);

INSERT INTO gerir_grupos VALUES (1,1,1);

CREATE TABLE Posts
(
    id_post int auto_increment not null primary key,
    id_utilizador int,
    Titulo_do_post varchar(50),
    Corpo_do_post varchar(500),
    FOREIGN KEY (id_utilizador) references Utilizadores (id_utilizador)
    
);
INSERT INTO posts VALUES (1,1,"teste","grupo teste");

CREATE TABLE Gerir_posts
(
    id_gerir_post int auto_increment not null primary key,
    id_post int,
    id_grupo int,
    FOREIGN KEY (id_post) references Posts (id_post) on delete cascade,
    FOREIGN KEY (id_grupo) references Grupos (id_grupo)on delete cascade
);

INSERT INTO gerir_posts VALUES (1,1,1);

If this can help

Picture Tables

if i understand you, this is called a cascading delete. You can add triggers to do this or you can define the database to do them - depends on which RDBMS you are using for hte syntax — Randy
– Randy, Commented Mar 30, 2017 at 17:04
i just wanna delete whats in the red boxes when i select an id_grupo from the table grupos. — Hazonstorm
– Hazonstorm, Commented Mar 30, 2017 at 17:09

9000 · Accepted Answer · 2017-03-30 20:25:52Z

3

You cannot specify many tables in a single delete statement.

Use a transaction

begin;
delete posts where post_id in (select post_id from gerir_posts where ...);
delete gerir_posts where ... ;    
delete grupos where ... ;
commit;

This way, everything is either deleted together, or stays intact.

Use `ON DELETE CASCADE` in your constraints

create table posts (
  post_id integer primary key,
  ...
);

create table gerir_posts (
  ...
  post_id integer;
  constraint gerir_posts_fk 
    foreign key(post_id) references(posts.post_id)
    on delete cascade,
);

Now if you delete a record from posts, all records in gerir_posts that refer to the same post_id are also deleted.

You can use alter table to add / modify constraints of the existing tables.

edited Mar 30, 2017 at 20:25

answered Mar 30, 2017 at 17:09

9000

41k9 gold badges69 silver badges108 bronze badges

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

12 Comments

9000 Over a year ago

These links will help you understand more: 1 2 3 4. Copying someone's code without understanding is dangerous.

9000 Over a year ago

You're one step from understanding how it works, and from having much easier time working with your database. Software developer's life is a constant study.

Hazonstorm Over a year ago

its my final project and i would like to learn always more , im asking for help to understand better code, i have lots of problems in my project step by step i will finish it i hope

9000 Over a year ago

If I were in your shoes I'd create two toy tables, like described above, and played with them, using insert, select, and delete. After the "aha!" moment, when you see how deleting a record from one table also deletes a record from another table, where the foreign key points to the first table, I'd update my work tables with confidence.

9000 Over a year ago

@user1827826: my contacts can be found in my profile.

|

Collectives™ on Stack Overflow

Delete from multiple tables in sql

1 Answer 1

Use a transaction

Use `ON DELETE CASCADE` in your constraints

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Use a transaction

Use ON DELETE CASCADE in your constraints

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Use `ON DELETE CASCADE` in your constraints