1 Replies - 14567 Views - Last Post: 08 October 2010 - 10:21 AM

#1 livium  Icon User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 519
  • Joined: 21-December 08

delete rows from two tables

Posted 06 October 2010 - 09:01 AM

If i have 2 tables like this:

t1

id (primary key)
val1

t2

id (foreign key)
val2

Ho can I delete let's say the id=300 from t1 and all the rows where id=300 from t2?

this does not work:
 delete from t1,t2 where t1.id=t2.id and t1.id=300 


it says: SQL Error: ORA-00933: SQL command not properly ended

Thanks!

This post has been edited by livium: 06 October 2010 - 09:02 AM


Is This A Good Question/Topic? 0
  • +

Replies To: delete rows from two tables

#2 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5881
  • View blog
  • Posts: 12,758
  • Joined: 16-October 07

Re: delete rows from two tables

Posted 08 October 2010 - 10:21 AM

You have to do "delete from SINGLE_TABLE where ..." Oracle isn't terribly flexible here.

You need to issue two statements. You'll also have to delete the child records first. On your foreign key constraint, you can specify "DELETE CASCADE" to have the children killed automatically. You can also write triggers to do the job, but only if the simple constraint version doesn't suit your needs.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1