Solved

SQL 2005 - DELETE statement conflicted with COLUMN SAME TABLE REFERENCE

Posted on 2009-05-18
8
619 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 7

Author Comment

by:Jeff S
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Found the issue ... splitting points to be fair.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

771 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

7 Experts available now in Live!

Get 1:1 Help Now