unable to delete invalid SQL Server records in Access or SQL Server

[Randall Arnold]

Thanks to a former dba who didn't set up cascading deletes between joined
tables, I have inherited a master table with 395 orphaned records. The
orphan state can only be determined when the master table is joined to the
table on the many side. I need to delete these 395 records and then enforce
referential integrity, but my delete query produces an error whether it's
run in Access (2003) or SQL Server (2005). The error states that the View
"is not updateable because the modification affects multiple base tables".
Odd thing is, I've been able to do this in similar situations.

Here is my join query:

SELECT dbo.InternalAudit.InternalAuditID,
dbo.InternalAuditFails.InternalAuditFailID
FROM dbo.InternalAudit LEFT OUTER JOIN
dbo.InternalAuditFails ON
dbo.InternalAudit.InternalAuditID = dbo.InternalAuditFails.InternalAuditID
WHERE (dbo.InternalAudit.PassAudit = 0)

That pulls in the good and bad records. My DELETE stored procedure is as
follows:

DELETE FROM dev.View0
WHERE (InternalAuditFailID IS NULL)

Seems pretty straight-forward to me, but no go.

Any ideas?

Thanks,

Randall Arnold

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't indicate which table you want to delete from.

If your "join query" is View0, then a DELETE on the View won't work 'cuz
it has 2 tables & the query optimizer can't determine in which table to
delete rows. Just use a single DELETE command instead of the view.
E.g.:


DELETE FROM orphan_table
WHERE id_column IN
(SELECT o.id_column
FROM orphan_table AS o LEFT JOIN parent_table AS p
ON o.id_column = p.id_column
WHERE p.id_column IS NULL)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+KHVIechKqOuFEgEQKBowCgl9SNch1z3R7V5/9dC29m1JDHQlsAoJDi
0CC3dsVZIoUBYm1rfoyXO3K+
=bAGI
-----END PGP SIGNATURE-----

 

[Randall Arnold]

Well, I tried the method you suggested and it didn't work. In fact, it
appears to be returning the opposite of what I'm looking for. I'm thinking
this may be because the column in the "many" table that needs to be null is
not the common id column; the orphan records are in the "one" table, not the
"many". Again, root cause has to do with the poor design by the original
dba, who neglected to allow cascading deletes and failed to include an Undo
feature in his data entry app. This has forced auditors to occasionally
cancel transactions and leave the orphan records in the main table.

Of course, I may have misunderstood what you meant. Here's my
interpretation of your suggestion:

DELETE FROM dbo.InternalAudit
WHERE InternalAuditID IN
(SELECT o.InternalAuditID
FROM dbo.InternalAudit AS o LEFT JOIN dbo.InternalAuditFails AS p
ON o.InternalAuditID = p.InternalAuditID
WHERE p.InternalAuditFailID IS NULL)

InternalAuditID is the common id field. InternalAuditFailID returns null in
my View0 if there are orphan records in the InternalAudit table. I'm sure
the necessary logic is clear, but I have a cold and can't think straight...
lol.

Thanks for any further help,

Randall Arnold

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

After I posted my answer to your question I realized I should have named
the "orphan_table" the "child_table," 'cuz the definition of orphans is
without parents. When you say the "main table" I'm assuming you're
referring to the parent table (the one side of a one-to-many
relationship). If that is the case, then the rows (records) in the
parent_table, that do not have related rows in the child table, are not
orphaned, they are childless rows.

To get rid of childless rows one would just reverse the logic of the
original DELETE command. IOW, look for rows in the parent table that
have values in the "linking" column (the column that establishes the
relationship between the parent & child tables) that are not in the
child table.

DELETE FROM dbo.InternalAudit
WHERE InternalAuditID NOT IN
(SELECT InternalAuditID
FROM dbo.InternalAuditFails)

BUT, since you are saying the column InternalAuditFailsID is the NULL
column - that just throws me. I don't understand the relationships
between the 2 tables. Could you show the DDL of the 2 tables & include
the relationships (Foreign Keys) between each?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+QfGIechKqOuFEgEQIbegCgu6/XR1bPfCLUF4zqGAgKqyaGkUYAn1eo
tGaeA2OrMIgCloJwolU0oblh
=bAAt
-----END PGP SIGNATURE-----

 

[Randall Arnold]

I took the "quick and dirty" route: I created a make Table query that output
the childless rows, then created a Delete query that deleted those rows from
the main table by using WHERE/IN. I couldn't get any other method to work.

Thanks for your help!

Randall Arnold