CODEWITHSUNDEEP
oracle-databasejoinsql-delete
fallingdog
fallingdog

Reputation: 192

delete on join not working as expected oracle

I am having a hard time getting my delete to work. I need to delete based on two columns in this case where the row exists in a source table.

I have 113843 rows in the table. My delete statement is:

delete from process.designer_mm_px_current_state 
where exists (select 1 from process.designer_mm_px_current_state dc
join
process.mm_px_current_state c
on dc.soid = c.soid and dc.state_id = c.state_id)

This reports 113843 rows deleted. But I have inserted a new row that is only in dc, so I should have one row at the end. Also:

select count(1) from process.designer_mm_px_current_state dc
join
process.mm_px_current_state c
on dc.soid = c.soid and dc.state_id = c.state_id

returns 113842 rows. Just as I expected. What am I missing? I am working in an oracle system...

Upvotes: 0

Views: 32

Answers (1)

Ctznkane525
Ctznkane525

Reputation: 7465

You have no reference between your inner and outer query. Try this instead. Dc is now your outer reference.

delete from process.designer_mm_px_current_state dc
where exists (select 1 from
process.mm_px_current_state c
Where dc.soid = c.soid and dc.state_id = c.state_id)

Upvotes: 2

Related Questions