Oracle DELETE sql with JOIN doesn't work

Question

My delete statement returns a 933 error in Oracle, I'm not sure what is wrong-

DELETE b
from temp a
JOIN
  fact_tab b
on a.col1 = b.col1
and a.col2 = b.col2
and a.col3 = b.col3;

Both tables dont have a primary key. select statement on the same thing works-

select * 
from temp a
JOIN
  fact_tab b
on a.col1 = b.col1
and a.col2 = b.col2
and a.col3 = b.col3;

Where in the manual did you find the syntax to use a JOIN for a DELETE statement? docs.oracle.com/cd/E11882_01/server.112/e41084/… — user330315
– user330315, Commented Feb 20, 2015 at 7:05
My mistake, I am not deeply familiar with Oracle. How would I accomplish this? — user2441441
– user2441441, Commented Feb 20, 2015 at 7:07

Okdel · Accepted Answer · 2015-02-20 07:07:43Z

3

try this

DELETE FROM
  fact_tab
WHERE
  EXISTS
  (
    SELECT
      1
    FROM
      temp
    WHERE
      temp.col1 = fact_tab.col1 AND
      temp.col2 = fact_tab.col2 AND
      temp.col2 = fact_tab.col2
  )

answered Feb 20, 2015 at 7:07

Okdel

1931 silver badge5 bronze badges

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

Comments

Lalit Kumar B · Accepted Answer · 2015-02-20 07:08:00Z

1

Oracle doesn't allow JOIN in a DELETE statement directly like that.

You could do the delete in the following way:

DELETE
FROM fact_tab
WHERE ROWID IN
  (SELECT b.rowid
  FROM temp a
  JOIN fact_tab b
  ON a.col1  = b.col1
  AND A.col2 = b.col2
  AND A.col3 = b.col3
  );

You could also use WHERE EXISTS clause.

answered Feb 20, 2015 at 7:08

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Collectives™ on Stack Overflow

Oracle DELETE sql with JOIN doesn't work

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related