Solved

SQL 2005 - DELETE statement conflicted with COLUMN SAME TABLE REFERENCE

Posted on 2009-05-18
8
625 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:Jeff S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24412071
error seems like you are trying to delete record from parent table without deleting its child record.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24412097
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
 
LVL 7

Author Comment

by:Jeff S
ID: 24412159
So I need to delete column 'VoidedParentId'? Not sure where I need to fix it in my SQL.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24412186
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
 
LVL 7

Author Comment

by:Jeff S
ID: 24412236
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
 
LVL 31

Accepted Solution

by:
RiteshShah earned 250 total points
ID: 24412252
you have to put that command in QUERY window of your SSMS, and yes replace tableName with your table name in your db.
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 250 total points
ID: 24412257
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
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 31582596
Found the issue ... splitting points to be fair.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question