Eliminating Duplicate rows within a table

[paulnlk4]

Hi,

I am not that experienced in SQL. Please help me.

I have the following scenario:

Within a table there is a possibility of having duplicate account numbers with different status

Example:

Account # Status
1 03
1 08
2 05
3 01
4 04
4 08

My specification state the following:

If one account is 08 and the other is not, keep the account that is not = 08.

The result I want to achieve is the following from above:


Account # Status
1 03
2 05
3 01
4 04

How can I do this in Teradata?

I actually want to update a existing work table using the Query.

I will appreciate if someone could guide me how to use Update statement to achieve the above result.

Thanks,
Paul

 

[BillDHS]

Maybe I'm being overly simplistic, but if you want to drop all status 8 it would be

Delete from table.name
where status = '8'

If you want to keep status 8 where it is the only value for the account it is more complicated.

 

[paulnlk4]

Hi,

I don't want to drop records which contain only Status with 08. Only those records need to be deleted which have duplicate Status.

For Example the following rows exist:

Account # Status

1 2
1 8
2 8

Result I want to see is:

Account # Status

1 2
2 8

If you notice, I want the record which has only one Row (i.e. Account # 2, with a Status code of 8 is retained, however from Account # 1, one of the rows which has a Status code of 8 is dropped.)

I hope that my requirements are now clear. Please respond.

Thanks,
Paul

 

[tdatgod]

Hi,
The problem is SQL is SET based logic. You can delete all the rows that qualify or None of the rows that qualify but not some of the rows that qualify.

Also how do you know you want to delete the 1,8 row? Why not the 1,2 row.

Who other than you knows which Status is the correct one?

Is there anyway to know from the data?

You can identify the duplicate data by

sel Account,Status from blort
where Account in
( sel Account from blort
group by 1
having count(Account) > 1);

Actually SQL isn't my strong suit so maybe someone can improve on this query.

 

[paulnlk4]

Once Again,

I want to drop records the following kinds of records:

Only those records need to be deleted which have duplicate Accounts with different Status codes and want to drop the one which has a Status of 8. If the 2 same Account numbers have 2 and 3 as Status Codes then I want to retain both, however, if it is 2 and 8, then I want to drop the row which has a Status of 8. This is a business requirment and I as the TA have to solve it.

For Example the following rows exist:

Account # Status

1 2
1 8
2 8

Result I want to see is:

Account # Status

1 2
2 8

If you notice, I want the record which has only one Row (i.e. Account # 2, with a Status code of 8 is retained, however from Account # 1, one of the rows which has a Status code of 8 is dropped, because this is a business requirment, which states that if i have duplicate rows with same account number and one of them has a Status of 8, then drop/delete that record.)

I hope that my requirements are now clear. Please respond.

Thanks,
Paul

 

[dnoeth]

You just have to translate your description in SQL:

delete
from accounts a1
where status = 8
and exists
(
select * from accounts a2
where
a1.account# = a2.account#
and status <> 8
)
;

Just replace &quot;delete&quot; with a &quot;select *&quot; to see which rows will be deleted ;-)

Dieter

 

[paulnlk4]

This SQL works! Thanks Dieter,

Paul