CODEWITHSUNDEEP
sqloracle-databasesql-delete
reza ramezani matin
reza ramezani matin

Reputation: 1474

subquery delete not working in oracle

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.

Upvotes: 1

Views: 171

Answers (3)

Bashir Zamani
Bashir Zamani

Reputation: 150

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

Upvotes: 1

ali akbar azizkhani
ali akbar azizkhani

Reputation: 2289

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)

Upvotes: 1

Michał M
Michał M

Reputation: 618

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;
)

Upvotes: 0

Related Questions