I need to remove duplicate columns, but it's not that easy

Question

Post reply

I need to remove duplicate columns, but it's not that easy

drewsx2

SSC Enthusiast

Points: 113
More actions
April 1, 2009 at 5:49 pm

#208137

Hello,
I have a Table in my DB that is used by multiple other tables. It looks like this:
ID Type User_ID
1
2
3
4
5
Drew

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

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

drewsx2 SSC Enthusiast Points: 113 More actions · Answer 1

Sorry Glich in first posting!

Hello,

I have a Table in my DB that is used by multiple other tables. It looks like this:

ID Type User_ID

1 Begin 23

2 End 24

3 Begin 45

4 Begin 46

5 End 47

The other tables use the Type in them. The problem is they all reference different 'Begin' values.

I need to remove all of them (Begin) but one and point the tables to that one instance. Is this possible?

Does this make since to anyone?

Drew

Lowell SSC Guru Points: 323516 More actions · Answer 2

drewsx2 (4/1/2009)
Sorry Glich in first posting!
Hello,
I have a Table in my DB that is used by multiple other tables. It looks like this:
ID Type User_ID
1 Begin 23
2 End 24
3 Begin 45
4 Begin 46
5 End 47
The other tables use the Type in them. The problem is they all reference different 'Begin' values.
I need to remove all of them (Begin) but one and point the tables to that one instance. Is this possible?
Does this make since to anyone?
Drew

yes, this is kind of a common data cleanup issue;

I've always done it with a lot on analysis, and never really automated it, so in your example i might do something like:

UPDATE OTHERTABLE

Set [FKIDThatReferencedTheTable] = 1 --the first "begin"

WHERE [FKIDThatReferencedTheTable] IN(3,4) --the other "begin" found in analysis

--repeat for all other tables that also reference that table

UPDATE OTHERTABLE

Set [FKIDThatReferencedTheTable] = 2 --the first "end"

WHERE [FKIDThatReferencedTheTable] IN(5) --the other "end" found in analysis

DELETE FROM MASTERTABLE WHERE ID IN(3,4) --remove duplicate "begin" items

DELETE FROM MASTERTABLE WHERE ID IN(5) --remove duplicate "end" items

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 3

Hi

This is one of the challenge. Its always good to have Uniques on Dimension/Enum/Lookup tables.

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Sk1ppy SSC Eights! Points: 810 More actions · Answer 4

What's the point of the User Id then? Is it needed (or referenced) for something anywhere else?