CODEWITHSUNDEEP
sql
Hazonstorm
Hazonstorm

Reputation: 1

Delete from multiple tables in sql

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

Upvotes: 0

Views: 143

Answers (1)

9000
9000

Reputation: 40884

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.

Upvotes: 3

Related Questions