mercredi 15 juin 2016

Deleting multiple rows in multiple tables only with query

So I need to delete a parent row, but has multiple child tables, the thing is I have to delete the childs first and without cascade on delete.

I've tried this, but I think I'm really messing it up.

delete linhasencomenda
  from linhasencomenda
    inner join encomendas
      on linhasencomenda.idEncomenda=encomendas.id
        where idCliente in
          (select idCliente from encomendas where idDistrito in
            (select idDistrito from clientes inner join distritos on clientes.idDistrito=distritos.id where distrito='Setúbal'));
delete encomendas
  from encomendas
    inner join linhasEncomenda
      on encomendas.id=linhasencomenda.idEncomenda
        where idDistrito in
          (select idDistrito from clientes inner join distritos on clientes.idDistrito=distritos.id where distrito='Setúbal';
delete utilizadores
  from utilizadores
    inner join clientes
      on utilizadores.idCliente=clientes.id
       where idDistrito in
          (select idDistrito from clientes inner join distritos on clientes.idDistrito=distritos.id where distrito='Setúbal';
delete clientes
  from clientes
    inner join distritos
      on clientes.idDistrito=distritos.id
        where distrito='Setúbal';

So in the end I really just need to delete every cliente with idDistrito='Setúbal'.

I apreciate any help.

Aucun commentaire:

Enregistrer un commentaire