removing a duplicate entry that is also a FK in another table

Question

Post reply

removing a duplicate entry that is also a FK in another table

yogiberr

Hall of Fame

Points: 3679
More actions
December 18, 2006 at 2:21 pm

#175297

hiya,

I have a table;
<tblProductCondition>
productCondtionId PK ID
productCondtionName
</tblProductCondition>
Now, I made an arse of it, and created 2x entries for "damaged",
ie
productCondtionId
0
1
productCondtionName
"damaged"
"damaged"
So, I need to remove one of these entries.The problem is that they are BOTH being used as foreign key fields in other tables.
I tried to run an update query, but sql server wnats me to remove the primaryKeys on the table..When I did that, I wan't able to add them again because it was complaining of duplicate indexes.
How can I get rid of this duplicate entry?I thought it would have been easy, but I'm struggling.

Please let me know if i can clarify.
many thanks,
yogi :-0

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

Try to delete after disabling the FK...

CREATE TABLE cnst_example

(id INT NOT NULL,

name VARCHAR(10) NOT NULL,

salary MONEY NOT NULL

CONSTRAINT salary_cap CHECK (salary < 100000)

)

-- Valid inserts

INSERT INTO cnst_example VALUES (1,'Joe Brown',65000)

INSERT INTO cnst_example VALUES (2,'Mary Smith',75000)

-- This insert violates the constraint.

INSERT INTO cnst_example VALUES (3,'Pat Jones',105000)

-- Disable the constraint and try again.

ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap

INSERT INTO cnst_example VALUES (3,'Pat Jones',105000)

-- Re-enable the constraint and try another insert; this will fail.

ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap

INSERT INTO cnst_example VALUES (4,'Eric James',110000) ;

http://msdn2.microsoft.com/en-us/library/ms190273.aspx

http://www.databasejournal.com/features/mssql/article.php/2219901

MohammedU
Microsoft SQL Server MVP

yogiberr Hall of Fame Points: 3679 More actions · Answer 2

ah ok, I think I understand.

I'll give it a go.

ta,

yogi

yogiberr Hall of Fame Points: 3679 More actions · Answer 3

hiya,

Ok, for this to work, I think I need a query that will SUM the products in tblReturnProducts that have productCondtionId = 0.

eg, for a return with an ID of "33", there might be..

returnId barCode returnProductConditionId qty

33, 123, 0, 4

33, 123, 0, 5

So, instead of having the above 2x rows, I need to have a SINGLE row that contains the qty as a SUM.

returnId barCode returnProductConditionId qty

33, 123, 0, 9

tblReturnProducts

returnId

barCode

returnProductConditionId

qty

</schema>

I hope that makes sense 🙂 Obviously, the query will ignore all products that don't have mutiple products with the same "returnProductConditionId"

Can anyone help?

many thanks,

yogi

Richard Josey Ten Centuries Points: 1076 More actions · Answer 4

My understanding of your initial problem is that you have some rows which had productCondtionId = 0 and some productCondtionId = 1

But both mean "damaged"

try:

update tblReturnProducts

set returnProductConditionId = 0

where returnProductConditionId = 1

go

This should associate all affected rows with 0 and not 1

Then:

Delete from tblProductCondition

where productCondtionId = 1

GO

Hope I am understanding your problem correctly. Good luck

yogiberr Hall of Fame Points: 3679 More actions · Answer 5

hiya,

Yes, but AFTER i have done that, I still have the hassle with tblReturnProducts.

the hassle is that there will be 2x rows of the

same product with the

same productConditionId within the

same return.

That is where I think I need my SUM query.

Make sense?

cheers,

yogi