October 18, 2006 at 2:35 pm
I want to delete a row from a table given the primary key for the row I want to delete IF that value isn't present in another table. Basically, I want to emulate the way a foreign key constraint would work. Unfortunately, the following code doesn't work--the record is NEVER deleted, even if the key doesn't exist in the other table:
DELETE FROM STUDENT WHERE STUDENT_PID=
AND STUDENT_SID=
AND STUDENT_SID NOT IN (SELECT INVOICE_SID FROM INVOICE);
Any ideas?
The problem might be easy to solve using an actual foreign key constraint, but I do sometimes need to have records in the INVOICE table that have a NULL INVOICE_SID. I don't think the foreign key constraint would allow that.
Thanks! =)
--TWH
October 18, 2006 at 2:56 pm
Initially it looks like you're trying to join STUDENT_SID to INVOICE_SID... is that what you're intending? In any case, you're better off using a join than a subquery, check NULL against any non-null field in INVOICE.
DELETE s
FROM STUDENT s
LEFT JOIN INVOICE i
ON s.STUDENT_SID = i.INVOICE_SID
WHERE i.(Primary Key) IS NULL
AND STUDENT_SID = cfsqltype="CF_SQL_INTEGER">
AND STUDENT_PID = cfsqltype="CF_SQL_INTEGER">
October 18, 2006 at 3:24 pm
Yes, that's correct RE: STUDENT_SID and INVOICE_SID. Why do you think a join is better? In this case the problem is that I want to delete the record only if the primary key for that record isn't used in another table. In other words, I don't want orphaned records in the INVOICE table because the parent records have been deleted from the STUDENT table. I only want the delete from STUDENT to work if there are no records in INVOICE that reference that STUDENT_SID.
--TWH
October 19, 2006 at 2:34 am
That's right, Tim.
LEFT JOIN with a check for NULL, as Aaron posted, is the preferred way of finding out records that have no connected records in another table.
JOINs are optimized in SQL Server, and should be used wherever possible. Clauses like IN (Select ...) are comparable in performance only if the number of rows returned for IN is very low, with large number of rows JOIN is many times quicker.
Actually, I would use the column on which JOIN is performed for the check:
DELETE s
FROM STUDENT s
LEFT JOIN INVOICE i
ON s.STUDENT_SID = i.INVOICE_SID
WHERE i.INVOICE_SID IS NULL
I omitted the other conditions, because I don't understand how they would work. Maybe that is where your problem lies... Could you explain what STUDENT_SID=<CFQUERYPARAM value="#URL.student_sid#"
cfsqltype="CF_SQL_INTEGER"> means? Maybe this is some part of SQL I'm ignorant of, but I really don't understand how this should filter the rows.
October 19, 2006 at 8:43 am
I agree with the previous post regarding the JOINS, but I try to avoid using LEFT JOINS when I can. Try this
DELETE STUDENT
FROM STUDENT s
WHERE s.STUDENT_SID = 'SOME_VALUE'
AND NOT EXISTS (SELECT 1 FROM INVOICE i
WHERE i.INVOICE_SID = s.STUDENT_SID
AND i.INVOICE_SID = 'SOME_VALUE')
The resulting plans are usually the same, but in practice, I have found using the EXISTS works better, specially when you have a large number of rows.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply