Need SQL help to remove repeated records...

UDA
Enthusiast

Need SQL help to remove repeated records...

I have a case like below:

Table_1 Table_2

A -> X
B -> X
C -> X
D -> Y
E -> Y

The table_1 columns are merged to the table_2 columns. I need to update the Table_1 column with the value from Table_2 (i.e. 'A' should be replaced with 'X'). But, I can't have 3 'X' in Table_1 because it's a primary index. So, I need to delete either of the 2 records from the Table_1 (can be either A & B or B & C or C & A) so that I can update the remaining one record with 'X'. Same way, I need to do for D & E.

Any suggestions?
2 REPLIES
SN
Enthusiast

Re: Need SQL help to remove repeated records...

hi,

You said you have a primary index - if it a non-unique PI then you can very well retain all records and do the udate.

If its a Unique PI, its obvious that table A will not have repeating values (i.e., no repeating A or B or C....).
If you want to retain only one record to replace with 'X' then you need to have a WHERE clause to identify the 'one' record to do the update or to delete all but one.

HTH,
Enthusiast

Re: Need SQL help to remove repeated records...

Thanks SN

I got over it by creating a multiset table and then using row_number function....