CODEWITHSUNDEEP
oracle-databaseplsql
Chatra
Chatra

Reputation: 3139

Oracle - Delete records with conditions

I would like to write SQL which deletes records based on a few conditions.

I have four recordsimage of records

In these records, ID is unique and can change. Name is used to delete the records.

1) A Name should have at least one OK type.

2) IF Name has an ID that doesn't contain DUMMY in its name, then delete all other records where ID contains DUMMY.

How can I do this in SQL?

Upvotes: 0

Views: 942

Answers (1)

forpas
forpas

Reputation: 164204

Try this:

delete from tablename t
where t."id" like '%DUMMY%'
and t."name" = (
  select "name" from tablename
  where "name" = '?'
  group by "name"
  having sum(case when "type" = 'OK' then 1 else 0 end) > 0
  and sum(case when "id" not like '%DUMMY%' then 1 else 0 end) > 0
)

The subquery after the IN clause returns the names that have at least 1 type = 'OK' and at least 1 id not containing 'DUMMY'.
Edit:

delete from tablename t
where
  t."name" = '?' 
  and (
    (t."id" like '%DUMMY%' and t."type" <> 'OK')
    or (
      t."type" = 'OK' 
      and exists (
        select 1 from tablename
        where "name" = t."name" and "type" = t."type" and "id" > t."id"
      )
    )
  )

Upvotes: 2

Related Questions