SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Sunday, June 25, 2006

Quick Method to delete from Two Tables

SQL Apprentice Question
I have two tables ( a & b ) Both are linked by a ledgerref field. table
what would be the quickest and easiest way to delete records from both when
a.textStatus = 1


Celko Answers
Getting it out of dialect, and correcting the "textStatus" data
element name (test and status are both suffixes to an attribute in
ISO-11179). I will not comment on the practice of using flags in SQL
to mimic an assembly language programming, or redundant tables to mimic
scratch tapes.

DELETE FROM Beta
WHERE EXISTS
(SELECT *
FROM Alpha
WHERE Beta.ledger_ref = Alpha.ledger_ref
AND Alpha.foobar_status = 1);


DRI action would be better. The best solution would be a proper
relational design.

No comments: