February 13, 2011 at 1:18 am
I AM TRYING TO DELETE CONTACT ID FROM CONTACT AND SALESHEADER TABLES USING OUTPUT.DELETED OPTION( CONTACTID IN SALESHEADER REFERS TO CONTACT TABLE). I AM NEWBIE TO SQL PLEASE HELP ME SOLVE
CAN ANYBODY HELP ME SOLVE
DECLARE @DeleteOutput3 TABLE (CONID INT PRIMARY KEY CLUSTERED)
DELETE Contact
OUTPUT
deleted.CONTACTID
INTO @DeleteOutput3
OUTPUT
deleted.CONTACTID
WHERE CONTACTID = 10;
DELETE FROM SalesHeader
FROM SalesHeader INNER JOIN
@DeleteOutput3 ON CTEID=CONTACTID
where CONTACTID=10
February 13, 2011 at 4:09 am
Except for the double usage of some parts of the statement it shoult work...
DECLARE @DeleteOutput3 TABLE (CONID INT PRIMARY KEY CLUSTERED)
DELETE Contact
OUTPUT
deleted.CONTACTID
INTO @DeleteOutput3
WHERE CONTACTID = 10;
DELETE FROM SalesHeader INNER JOIN
@DeleteOutput3 d ON SalesHeader.CTEID=d.CONID
As a side note: please don't post all in capital letters. It's considered as yelling. But there's no reason to...;-)
February 13, 2011 at 4:44 am
Hey i just checked with this query given by you
still i am getting error message. Could you please help me to resolve this issue
Msg 547, Level 16, State 0, Line 3
The DELETE statement conflicted with the REFERENCE constraint "FK_SalesHeader_PID". The conflict occurred in database "updates", table "dbo.SalesHeader", column 'CONTACTID'.
The statement has been terminated.
February 13, 2011 at 4:45 am
the script
DECLARE @DeleteOutput3 TABLE (CONID INT PRIMARY KEY CLUSTERED)
DELETE Contact
OUTPUT
deleted.CONTACTID
INTO @DeleteOutput3
WHERE CONTACTID = 10;
DELETE SALESHEADER FROM SalesHeader INNER JOIN
@DeleteOutput3 d ON SalesHeader.CONTACTID=d.CONID
February 13, 2011 at 5:46 am
praveensc2003 (2/13/2011)
Hey i just checked with this query given by youstill i am getting error message. Could you please help me to resolve this issue
Msg 547, Level 16, State 0, Line 3
The DELETE statement conflicted with the REFERENCE constraint "FK_SalesHeader_PID". The conflict occurred in database "updates", table "dbo.SalesHeader", column 'CONTACTID'.
The statement has been terminated.
The query itself is still a valid statement.
The error you get is because you have a foreign key reference that prevents some data to be deleted from dbo.SalesHeader.
Check where the named reference points to and delete the rows in there as well (if this is intended...)
February 13, 2011 at 5:53 am
thanks for the inputs
February 15, 2011 at 12:04 am
Hi
i am not able solve this problem. can any body guide how to solve this. send me sample script.
thanks
February 15, 2011 at 10:36 am
praveensc2003 (2/15/2011)
Hii am not able solve this problem. can any body guide how to solve this. send me sample script.
thanks
It's hard for us to provide you a script that would resolve the foreign key conflict without seeing all table defs involved (including indexes and foreign key referneces).
If you can't resolve the issue I strongly recommend you talk to one of your DBA's or another person familiar with the database design. You might also consider calling a consultant in for a few hours.
It has nothing to do with T-SQL syntax or code concept. All that's causing it are constraints in your DB design we have no information about.
February 15, 2011 at 11:17 am
I suspect SalesHeader has a foreign key to Contact. Try:
DELETE SalesHeader WHERE ContactId = 10
DELETE Contact WHERE ContactId = 10
February 15, 2011 at 11:38 am
as simple as that
it worked
thanks
praveen
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply