Solved

SQL 2005 - DELETE statement conflicted with COLUMN SAME TABLE REFERENCE

Posted on 2009-05-18
8
621 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
  • 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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

862 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now