Easy & safe deletion of inconsistent rows

[codeman]

Hi all

Lets say we have two tables:

Customer:
Customer_number : Decimal(15)
Name : Char(30)

Purchase:
Purchase_number : Decimal(15)
Customer_number : Decimal(15)

There is a relationship between Purchase and Customer, so that each
purchase must belong to a customer. That is, Customer_number in the
Purchase table must match a row in the Customer table (with the same
customer_number) - a foreign key.

This relationship is not enforced by the database.

One day you discover, that not all purchases has a relationship to an
existing customer. That is, the database is inconsistent. First you make a
select to find the inconsistent rows:

select *
from Purchase p
left outer join Customer c
on p.customer_number = c.customer_number
where c.customer_number is null

Lets say, you find 20 similar unenforced relationships (between other
tables). All containing inconsistent rows. You make 20 select statements
to discover all the inconsistencies.

The next step is to delete the inconsistencies. And this is my question.
How do one safely (be sure to only delete inconsistent rows) and easily
delete the inconsistencies? It is a requirement, that one would be able to
see which rows has been deleted. You have a typical IBM MVS/TSO
environment at your disposal. That is DB2, COBOL, JCL, Spufi, QMF, ...
Greetings,

Mads

[jefftyzzer]

Hi.

I'm in an LUW environment. Assuming you have modifying table functions
at your disposal, I'd try something like the following (you might want
to turn off autocommit first, if that applies):

SELECT
*
FROM
OLD TABLE
(
DELETE FROM
PURCHASES P
WHERE
NOT EXISTS
(
SELECT
1
FROM
CUSTOMERS C
WHERE
C.CUSTOMER_NUMBER = P.CUSTOMER_NUMBER
)
)

If modifying table functions aren't available in IBM MVS/TSO, then
you'd have to execute a separate query first (just as you already did).
The DELETE should translate as-is, though.

--Jeff

codeman wrote:

Hi all

Lets say we have two tables:

Customer:
Customer_number : Decimal(15)
Name : Char(30)

Purchase:
Purchase_number : Decimal(15)
Customer_number : Decimal(15)

There is a relationship between Purchase and Customer, so that each
purchase must belong to a customer. That is, Customer_number in the
Purchase table must match a row in the Customer table (with the same
customer_number) - a foreign key.

This relationship is not enforced by the database.

One day you discover, that not all purchases has a relationship to an
existing customer. That is, the database is inconsistent. First you make a
select to find the inconsistent rows:

select *
from Purchase p
left outer join Customer c
on p.customer_number = c.customer_number
where c.customer_number is null

Lets say, you find 20 similar unenforced relationships (between other
tables). All containing inconsistent rows. You make 20 select statements
to discover all the inconsistencies.

The next step is to delete the inconsistencies. And this is my question.
How do one safely (be sure to only delete inconsistent rows) and easily
delete the inconsistencies? It is a requirement, that one would be able to
see which rows has been deleted. You have a typical IBM MVS/TSO
environment at your disposal. That is DB2, COBOL, JCL, Spufi, QMF, ...
Greetings,

Mads

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. Here are my guessses at what you meant:

CREATE TABLE Customers
(cust_nbr DECIMAL (15) NOT NULL PRIMARY KEY,
cust_name CHAR (30) NOT NULL)

CREATE TABLE Purchases
(purchase_nbr DECIMAL (15) NOT NULL PRIMARY KEY,
cust_nbr DECIMAL (15) NOT NULL
REFERENCES Customers (cust_nbr)
ON UPDATE CASCADE);

or did you mean:

CREATE TABLE Purchases
(purchase_nbr DECIMAL (15) NOT NULL,
cust_nbr DECIMAL (15) NOT NULL
REFERENCES Customers (cust_nbr)
ON UPDATE CASCADE,
PRIMARY KEY (purchase_nbr, cust_nbr));

>This relationship is not enforced by the database. <<

The guy that failed to add the REFERENCES clause and caused this
problem has been fired by now, right? Likewise the use of singular
table names has been corrected.

>The next step is to delete the inconsistencies. <<

DELETE FROM Purchases
WHERE NOT EXISTS
(SELECT *
FROM Customers AS C
Purchases.cust_nbr = P.cust_nbr);

>It is a requirement, that one would be able to see which rows has been deleted. <<

You that before with

SELECT P.*
FROM Purchases AS P
WHERE NOT EXISTS
(SELECT *
FROM Customers AS C
P.cust_nbr = P.cust_nbr);

An EXISTS () is usually faster than the LEFT OUTER JOIN you were using;
there is no need to materialize a result set.

The final is to add the missing constraints -- mop the floor AND fix
the leak!

[Robert]

codeman wrote:

Hi all

Lets say we have two tables:

Customer:
Customer_number : Decimal(15)
Name : Char(30)

Purchase:
Purchase_number : Decimal(15)
Customer_number : Decimal(15)

There is a relationship between Purchase and Customer, so that each
purchase must belong to a customer. That is, Customer_number in the
Purchase table must match a row in the Customer table (with the same
customer_number) - a foreign key.

This relationship is not enforced by the database.

One day you discover, that not all purchases has a relationship to an
existing customer. That is, the database is inconsistent. First you make a
select to find the inconsistent rows:

select *
from Purchase p
left outer join Customer c
on p.customer_number = c.customer_number
where c.customer_number is null

Lets say, you find 20 similar unenforced relationships (between other
tables). All containing inconsistent rows. You make 20 select statements
to discover all the inconsistencies.

The next step is to delete the inconsistencies. And this is my question.
How do one safely (be sure to only delete inconsistent rows) and easily
delete the inconsistencies? It is a requirement, that one would be able to
see which rows has been deleted. You have a typical IBM MVS/TSO
environment at your disposal. That is DB2, COBOL, JCL, Spufi, QMF, ...
Greetings,

Mads

The others have made appropriate suggestions about the technical
aspects, but I question the business logic. Why have you got purchases
orphaned from customers, are the purchases real? If so then you should
be looking to reinstate the associated customer details, if only with
just the name that is available from the purchase details. Even if
this is just a training exercise, the principle still holds, if the
details are real facts, then deleting them because they are orphaned
just creates more trouble down the line.

Robert