Reputation: 1474
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
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
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