En SQL Server, per eliminar registres d’una taula seguint un criteri relacionat amb una altra taula de la base de dades, es pot establir una join entre les dues taules dins de la mateixa sentència SQL de Delete.
És molt semblant al mètode per fer un update a partir d’una join per actualitzar registres segons valors d’altres taules, però en aquest cas, com que es tracta d’eliminar registres en lloc d’actualitzar-los, la sintaxi SQL canvia una mica.
Si volem fer un delete de registres utilitzant una join, l’únic que cal fer és especificar a la primera línia de la sentència la taula o taules on volem aplicar l’eliminació de registres, i a la línia següent, començant amb FROM, la join de les taules que necessitem, i el criteri indicat amb un WHERE.
Exemple SQL de Delete amb Inner Join
Un exemple senzill d’un delete amb una join entre taules per aplicar el criteri d’esborrat:
DELETE TaulaAmbRegistresAEliminar FROM TaulaAmbRegistresAEliminar TDel INNER JOIN TaulaPerCriteriEliminacio TFiltro ON TDel.idCamp_fk = TFiltro.idCamp_pk WHERE TFiltro.criteri = 'Valor per filtrar'
Delete amb Outer Join per eliminar registres sense referències en una altra taula
Aquesta opció d’utilitzar una inner join per eliminar registres es pot utilitzar amb tots els tipus de join. Per exemple, combinant-ho amb una outer join, és molt útil per eliminar registres d’una taula que no tinguin identificador a una altra taula, és a dir, registres que no tinguin correspondència o no existeixin a l’altra taula.
Només cal fer una left join amb la taula on tenim els identificadors de referència, i tots els registres resultants que tinguin un valor nul per a la taula enllaçada són aquells el valor dels quals no es troba a la segona taula, és a dir, els que volem eliminar de la primera.
Com sempre, amb un exemple SQL per esborrar registres que no existeixen en una altra taula s’entendrà millor:
DELETE TaulaAmbRegistresAEliminar FROM TaulaAmbRegistresAEliminar TDel LEFT JOIN TaulaAmbRegistresCorrectes TMaestra ON TDel.idCamp_fk = TMaestra.idCamp_pk WHERE TMaestra.idCamp_pk IS NULL
Delete amb join sobre diverses taules per a esborrat en cascada
En molts casos pot interessar eliminar els registres de la taula inicial i també els registres de la taula amb la qual es fa la join per establir el criteri de selecció. Seria com una eliminació en cascada dels registres d’ambdues taules.
Per eliminar registres de diverses taules alhora, només cal incloure a la primera línia de la sentència les taules de les quals volem eliminar registres.
Per l’exemple SQL de delete anterior, si volguéssim esborrar també els registres de la taula ‘enllaçada’ a l’inner join per fer una eliminació en cascada, només caldria incloure la segona taula a la línia DELETE, així:
DELETE TaulaAmbRegistresAEliminar, TaulaPerCriteriEliminacio FROM TaulaAmbRegistresAEliminar TDel INNER JOIN TaulaPerCriteriEliminacio TFiltro ON TDel.idCamp_fk = TFiltro.idCamp_pk WHERE TFiltro.criteri = 'Valor per filtrar'
Precaucions abans d’efectuar un delete
Una bona pràctica molt recomanable abans d’efectuar un esborrat de registres, a banda de les habituals còpies de seguretat implementades a la base de dades, és consultar sempre els registres que s’eliminaran per evitar sorpreses desagradables un cop eliminats.
La solució és tan senzilla com crear primer la sentència de selecció, i després de visualitzar i validar els registres a eliminar, convertir el select en un delete i procedir amb l’esborrat. Per més seguretat, fins i tot pots fer una còpia temporal en una altra taula justament dels registres que eliminaràs, per si més endavant descobreixes que algun d’ells (o tots) no l’hauries d’haver eliminat i necessites recuperar-lo ràpidament.
Amb la sintaxi abans comentada és tan fàcil com substituir la línia ‘delete [nom_taula]’ per una línia ‘select *’, i si vols la màxima seguretat i guardar aquests registres, només has d’afegir un ‘into [nom_taula_backup]’ a la línia ‘select *’.
Jo, a més, sempre faig servir línies de comentaris per treballar tot sobre la mateixa query i assegurar-me que no hi hagi diferències entre el select i el delete.
Anem a veure-ho pas a pas amb el primer exemple del post, l’exemple simple de delete amb join:
1. Selecció dels registres que vull eliminar
Canvio el delete (comentat) per select i visualitzo les dades a esborrar:
-- DELETE TaulaAmbRegistresAEliminar SELECT * FROM TaulaAmbRegistresAEliminar TDel INNER JOIN TaulaPerCriteriEliminacio TFiltro ON TDel.idCamp_fk = TFiltro.idCamp_pk WHERE TFiltro.criteri = 'Valor per filtrar'
2. Desa els registres en una taula
Per si després necessito recuperar alguna cosa o consultar què he esborrat:
-- DELETE TaulaAmbRegistresAEliminar SELECT * INTO CòpiaRegistresAEliminar_yyyymmdd FROM TaulaAmbRegistresAEliminar TDel INNER JOIN TaulaPerCriteriEliminacio TFiltro ON TDel.idCamp_fk = TFiltro.idCamp_pk WHERE TFiltro.criteri = 'Valor per filtrar'
3. Elimino els registres
Ara tinc més garanties de no fer un desastre ;)
DELETE TaulaAmbRegistresAEliminar -- SELECT * FROM TaulaAmbRegistresAEliminar TDel INNER JOIN TaulaPerCriteriEliminacio TFiltro ON TDel.idCamp_fk = TFiltro.idCamp_pk WHERE TFiltro.criteri = 'Valor per filtrar'
I si al final m’he equivocat i he de recuperar els registres eliminats?
Espero que aquest petit mètode sigui d’ajuda, no costa gens fer-ho i pot evitar molts problemes. Fer marxa enrere després d’un esborrat sempre és un tema delicat que pot arribar a complicar-se molt.
En el nostre cas, si hem tingut la precaució de guardar abans en una taula els registres que eliminarem, la recuperació d’un o més registres eliminats serà tan senzilla com fer un insert en aquesta taula amb un select sobre la taula on hem copiat les dades a eliminar.
Una query com aquesta pot evitar haver de demanar al nostre DBA la restauració d’una còpia de seguretat:
INSERT INTO TaulaAmbRegistresAEliminar SELECT * FROM CòpiaRegistresAEliminar_yyyymmdd WHERE ...
