subquery delete not working in oracle

Question

I have the following query which is joined and left joined:

   select aad.id
                  from [table1] aad
                  left outer join [table2] itm
                    on aad.table2_id = itm.id
                  left outer join [table3] eac
                    on aad.id = eac.table1_id
                  LEFT JOIN [table4] ces
                    ON eac.car_id = ces.id
                  LEFT join [table5] ci
                    on ces.car_information_id = ci.id
                 INNER join [table6] groupBi
                    on aad.capatibilty_degree_group_id = groupBi.id
                 where ces.id is null
                   and aad.depot_ammu_estimate = 123

The result of the above query is:

id
-----
2433
2431

Ids of [table1] table(aad.id) then I want to delete this records of that table then I query following syntax:

delete
  FROM [table1] w
 where w.id in (select aad.id
                  from [table1] aad
                  left outer join [table2] itm
                    on aad.table2_id = itm.id
                  left outer join [table3] eac
                    on aad.id = eac.table1_id
                  LEFT JOIN [table4] ces
                    ON eac.car_id = ces.id
                  LEFT join [table5] ci
                    on ces.car_information_id = ci.id
                 INNER join [table6] groupBi
                    on aad.capatibilty_degree_group_id = groupBi.id
                 where ces.id is null
                   and aad.depot_ammu_estimate = 123)

What is it happen, there are no records to delete. I don't know that what is happening that the above query does not delete the records.

Mayby You've already delete them, but did not commit/rollback changes? — Michał M
– Michał M, Commented Aug 3, 2016 at 12:36
Should work fine. Like was commented, you're probably not checking the results correctly or not committing, etc. If you want further help, please provide a minimal but complete script that we can use to reproduce your problem. — sstan
– sstan, Commented Aug 3, 2016 at 12:41
All these LEFT JOINs make little sense to me; you basically want an exists(). And the LEFT JOIN ... where ces.id is null can be rewitten into a not exists() And it appears you don't need the LEFT join [table5] ci on ces.car_information_id = ci.id term at all. — wildplasser
– wildplasser, Commented Aug 3, 2016 at 13:04

teo van kot · Accepted Answer · 2016-08-03 13:36:20Z

1

replace ces.id is null with nvl(ces.id,0) = 0

edited Aug 3, 2016 at 13:36

teo van kot

12.5k10 gold badges41 silver badges73 bronze badges

answered Aug 3, 2016 at 13:10

Bashir Zamani

1601 gold badge2 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ali akbar azizkhani · Accepted Answer · 2016-08-03 13:04:56Z

i think your problem is in using "is null" in query .i do not know why happen this problem

delete
  FROM [table1] w
 where w.id in (select aad.id
                  from [table1] aad
                  left outer join [table2] itm
                    on aad.table2_id = itm.id
                  left outer join [table3] eac
                    on aad.id = eac.table1_id
                  LEFT JOIN [table4] ces
                    ON eac.car_id = ces.id
                  LEFT join [table5] ci
                    on ces.car_information_id = ci.id
                 INNER join [table6] groupBi
                    on aad.capatibilty_degree_group_id = groupBi.id
                 where nvl(ces.id,0)=0 
                   and aad.depot_ammu_estimate = 123)

Michał M · Accepted Answer · 2016-08-03 12:55:26Z

0

I don't have testing env to work with syntax, but try EXIST clause. Something like this.

DELETE FROM [table1] t WHERE
EXISTS
(
  select 1
                  from [table1] aad
                  left outer join [table2] itm
                    on aad.table2_id = itm.id
                  left outer join [table3] eac
                    on aad.id = eac.table1_id
                  LEFT JOIN [table4] ces
                    ON eac.car_id = ces.id
                  LEFT join [table5] ci
                    on ces.car_information_id = ci.id
                 INNER join [table6] groupBi
                    on aad.capatibilty_degree_group_id = groupBi.id
                 where ces.id is null
                   and aad.depot_ammu_estimate = 123 
and aad.id=t.id;
)

edited Aug 3, 2016 at 12:55

answered Aug 3, 2016 at 12:42

Michał M

6185 silver badges14 bronze badges

Collectives™ on Stack Overflow

subquery delete not working in oracle

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related