CODEWITHSUNDEEP
sqlpostgresql
Geethurv
Geethurv

Reputation: 11

Postgres: Deleting rows that are duplicated in one column based on the conditions of another column

I have a PostgreSQL table that stores user details called users as shown below

ID  |  user name       | item   |  dos     | Charge|
1   |      Ed          |   32   |01-02-1987| 1     |
2   |            Taya  |     01 |05-07-1981|-1     |
3   |           Damian |     32 |22-19-1990| 1     |
2   |            Taya  |     01 |05-07-1981| 1     |
2   |            Taya  |     01 |05-07-1981| 1     |
1   |             Ed   |     32 |01-02-1987|-1     |

I want to delete rows where they are same across id, username, item and dos & sum of charges is 0. This means both row 1 and row 6 for ed gets deleted.

With more than 2 occurences, if the sum of charge is 1, i want one of the row with charge -1 and 1 deleted which means one row with charge 1 will be retained. For eg: ROw 2 and Row for Taya will be deleted.

The output table that i am after is:

ID  |  user name       | item   |  dos     | Charge|
3   |           Damian |     32 |22-19-1990| 1     |
2   |            Taya  |     01 |05-07-1981| 1     |

Any ideas?

Upvotes: 1

Views: 668

Answers (1)

Hambone
Hambone

Reputation: 16377

You want the having clause:

This will get you the output you want:

select
  id, user_name, item, dos, sum (charge)
from table
group by
  id, user_name, item, dos
having
  sum (charge) != 0

If you're really trying to delete the records that make it zero:

delete from table
where (id, user_name, item, dos) in (
  select id, user_name, item, dos
  from table
  group by id, user_name, item, dos
  having sum (charge) = 0
)

This does the same thing, and is quite a bit more code, but because it's using a semi-join it might be better for really large datasets:

with delete_me as (
  select id, user_name, item, dos
  from table
  group by id, user_name, item, dos
  having sum (charge) = 0
)
delete from table t
where exists (
  select null
  from delete_me d
  where
    t.id = d.id and
    t.user_name = d.user_name and
    t.item = d.item and
    t.dos = d.dos
)

Upvotes: 2

Related Questions