Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jchaves
Starting Member
3 Posts |
 Posted - 2012-05-16 : 16:57:52
|
| HelloI working with a table that is a cross-reference between IDs. Each ID has records associating it with other IDs. For example:ID RefIDA20 A22A20 A23What I want to do is to eliminate the redundant records. For example the two records below are - for all intended purposes - identical:ID RefIDA20 A22A22 A20How can I eliminate those?Thanks! |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-05-16 : 17:39:10
|
| If this is a table, add a computed column.create table t3(x1 varchar(3) not null,x2 varchar(3) not null,x3 as case when x1< x2 then cast(x1 as varchar)+','+cast(x2 as varchar) else cast(x2 as varchar)+','+cast(x1 as varchar) end unique );insert into t3(x1,x2) values ('A20','A22');insert into t3(x1,x2) values ('A22','A20'); |
 |
|
|
jchaves
Starting Member
3 Posts |
Posted - 2012-05-17 : 09:02:20
|
| InfluentMany thanks for the reply. Not sure I understand the purpose of the calculated field, specially when you check if x1 is less than x2. They are both strings so, can you explain the logic behind it?Thank you! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-17 : 09:47:42
|
| The CASE will always construct the same value regardless of how the pairs are stored, and the computed column adds a unique constraint to ensure only one row of the same pair is stored. You'll notice the 2nd row throws an error when you try to insert it. If you switch the INSERT statements the same thing will occur, even though the column values are switched. |
|
|
|
jchaves
Starting Member
3 Posts |
Posted - 2012-05-17 : 10:36:02
|
| Yes, I did notice that however, I could not use an INSERT INTO t3 SELECT x,y FROM t4 to populate t3 as the process errors out at the first time it finds a duplicate. Any suggestions?Thanks! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-17 : 10:45:05
|
| INSERT INTO t3 SELECT DISTINCT case when x < y then x else y end, case when y < x then x else y end FROM t4edit: added DISTINCT |
|
|
|
|
|
|
|
|
Eliminate redundant records