SQL 2005 - DELETE statement conflicted with COLUMN SAME TABLE REFERENCE

Update Script Error:

Server: Msg 547, Level 16, State 1, Procedure trDeletePatientVisitProcs, Line 28
DELETE statement conflicted with COLUMN SAME TABLE REFERENCE constraint 'FK_PVP_VoidedParent'. The conflict occurred in database 'MHHC', table 'PatientVisitProcs', column 'VoidedParentId'.
The statement has been terminated.

Any help is appreciated.
DECLARE
        @pvID INT ,
        @BatchID INT ,
        @Status INT ,
        @pvpID INT ,
        @BatchID2 INT ,
        @Status2 INT
 
 
-- First delete any 4013's that have been added to visits where a 1610 already exists and update the Value Code
DECLARE c CURSOR
        FOR SELECT DISTINCT
                pvp.patientvisitid
            FROM
                patientvisitprocs pvp
                JOIN patientvisitprocs pvp1 ON pvp.patientvisitid = pvp1.patientvisitid
            WHERE
                pvp.proceduresID = 15209
                AND pvp.cptcode = '4013'
                AND pvp.dateofservicefrom < '06/01/2008'
                AND pvp1.proceduresID = 2910
                AND ISNULL(pvp.Voided , 0) = 0
                AND ISNULL(pvp1.Voided , 0) = 0
 
OPEN c
FETCH NEXT FROM c INTO @pvid
 
WHILE @@FETCH_STATUS = 0
      BEGIN
 
            SELECT
                @BatchID = pvp.BatchID ,
                @Status = Status ,
                @pvpID = pvp.PatientVisitProcsID
            FROM
                Batch b
                INNER JOIN PatientVisitProcs pvp ON b.BatchID = pvp.BatchID
            WHERE
                pvp.proceduresid = 15209
                AND pvp.CPTCode = '4013'
                AND pvp.PatientVisitID = @pvID
 
            SELECT TOP 1
                @BatchID2 = pm.BatchID ,
                @Status2 = b.Status
            FROM
                PatientVisitProcs pvp
                INNER JOIN TransactionDistributions td ON pvp.PatientVisitProcsID = td.PatientVisitProcsID
                INNER JOIN Transactions t ON td.TransactionsID = t.TransactionsID
                INNER JOIN VisitTransactions vt ON t.VisitTransactionsID = vt.VisitTransactionsID
                INNER JOIN PaymentMethod pm ON vt.PaymentMethodID = pm.PaymentMethodID
                INNER JOIN Batch b ON pm.BatchID = b.BatchID
            WHERE
                pvp.proceduresid = 15209
                AND pvp.CPTCode = '4013'
                AND pvp.PatientVisitID = @pvID
 
            IF @Status <> 0 
               UPDATE
                Batch
               SET
                Status = 0
               WHERE
                BatchID = @BatchID
 
            IF @Status2 <> 0 
               UPDATE
                Batch
               SET
                Status = 0
               WHERE
                BatchID = @BatchID2
 
 
            EXEC trDeletePatientVisitProcs @pvpID
            UPDATE
                PatientVisitFiling
            SET 
                ValueCode0MID = 4443 ,
                ValueCode0Amount = 1610.0000
            WHERE
                PatientVisitID = @pvID
 
            IF @Status <> 0 
               UPDATE
                Batch
               SET
                Status = @Status
               WHERE
                BatchID = @BatchID
 
            IF @Status2 <> 0 
               UPDATE
                Batch
               SET
                Status = @Status2
               WHERE
                BatchID = @BatchID2
 
            FETCH NEXT FROM c INTO @pvid
 
      END
 
CLOSE c
DEALLOCATE c
 
-- Now update the patient visits before 6/1/2008 that have a 4013 and should have a 1610
DECLARE @ProceduresID INT
 
SELECT
    @ProceduresID = 2910
 
DECLARE c CURSOR
        FOR SELECT DISTINCT
                pvp.patientvisitid
            FROM
                patientvisitprocs pvp
                JOIN patientvisit pv ON pvp.patientvisitid = pv.patientvisitid
                JOIN patientvisitfiling pvf ON pvp.patientvisitid = pvf.patientvisitid
            WHERE
                pvp.proceduresID IN ( 2910 , 15209 )
                AND pvp.dateofservicefrom < '06/01/2008'
                AND ISNULL(pvp.Voided , 0) = 0
                AND pv.BillStatus IN ( 5 , 6 , 7 , 8 , 9 )
                AND (
                      pvp.CPTCode <> '4013'
                      OR pvp.Code <> '4013'
                      OR ValueCode0Amount <> 1610.0000
                      OR pvf.ValueCode0MID IS NULL
                    ) 
 
OPEN c
FETCH NEXT FROM c INTO @pvid
 
WHILE @@FETCH_STATUS = 0
      BEGIN
 
            SELECT
                @BatchID = pvp.BatchID ,
                @Status = Status ,
                @pvpID = pvp.PatientVisitProcsID
            FROM
                Batch b
                INNER JOIN PatientVisitProcs pvp ON b.BatchID = pvp.BatchID
                INNER JOIN PatientVisit pv ON pvp.PatientVisitID = pvp.PatientVisitID
            WHERE
                pvp.proceduresID IN ( 2910 , 15209 )
                AND pvp.PatientVisitID = @pvID
                AND pv.BillStatus IN ( 5 , 6 , 7 , 8 , 9 )
                AND ISNULL(pvp.Voided , 0) = 0
 
 
            IF @Status <> 0 
               UPDATE
                Batch
               SET
                Status = 0
               WHERE
                BatchID = @BatchID
 
            UPDATE
                PatientVisitProcs
            SET 
                Code = '4013' ,
                CPTCode = '4013'
            WHERE
                PatientVisitProcsID = @pvpID
            UPDATE
                PatientVisitFiling
            SET 
                ValueCode0MID = 4443 ,
                ValueCode0Amount = 1610.0000
            WHERE
                PatientVisitID = @pvID
 
            IF @Status <> 0 
               UPDATE
                Batch
               SET
                Status = @Status
               WHERE
                BatchID = @BatchID
 
 
            FETCH NEXT FROM c INTO @pvid
 
      END
 
CLOSE c
DEALLOCATE c

Open in new window

LVL 7
Jeff SAsked:
Who is Participating?
 
RiteshShahConnect With a Mentor Commented:
you have to put that command in QUERY window of your SSMS, and yes replace tableName with your table name in your db.
0
 
RiteshShahCommented:
error seems like you are trying to delete record from parent table without deleting its child record.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
DELETE statement conflicted with COLUMN SAME TABLE REFERENCE constraint 'FK_PVP_VoidedParent'. The conflict occurred in database 'MHHC', table 'PatientVisitProcs', column 'VoidedParentId'.

Hope you have a parent_column defined for each records.
And the Record which you try to delete was defined as parent id for some other record.

You have to delete it first before deleting this record as this was referenced by your Foreign key FK_PVP_VoidedParent. Delete that record first and delete this one.

Hope this helps
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Jeff SAuthor Commented:
So I need to delete column 'VoidedParentId'? Not sure where I need to fix it in my SQL.
0
 
RiteshShahCommented:
follow below command

sp_help TableName

It will show you which field of which table reference your VoidedParentID, you have to delete related data from that table first, after that you will be able to delete from your parent table.
0
 
Jeff SAuthor Commented:
RiteshShah -
Please excuse my ignorance .... sp_help TableName -> Where am I putting this? Am I replacing the "TableName" with a table name in my db? I've never used that one before.
0
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Making it more clear now:

1. From your delete statement, find what is your VoidedParentId.
2. Delete that record.
3. Try deleting your actual record now.

If you find the same error again while executing step 2 iterate steps 1 to 3 till you succeed.
0
 
Jeff SAuthor Commented:
Found the issue ... splitting points to be fair.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.