Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: delete with join query

    Hi,

    Please give the Oracle equivalent for the below SQL Server query :

    delete from test2 from test2 a inner join test3 b on a.sno = b.sno where a.sno =1 and b.sno = 1

    Here 2nd from clause is just like condition and based on this condition records will be deleted from the first from clause table

    Thanks,
    Sam

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    delete from test2 a
    where a.sno in (select b.sno from test3 b where b.sno=1)
    and a.sno = 1 ;

    Presumably that is a "contrived example", since it is equivalent to one of:

    delete from test2 a
    where a.sno in (select b.sno from test3 b where b.sno=1);

    delete from test2 a
    where a.sno = 1;

  3. #3
    Join Date
    Mar 2004
    Posts
    205
    Hi,

    Thanks for ur mail.

    delete from (select * from test2 a, test3 b where b.sno = a.sno and a.sno = 3 and b.sno = 3)

    How abt the above, I tried the above, its working and always it deletes from test2 table. How is this happening. Please explain. If I need to delete from test3 table what change should I do.

  4. #4
    Join Date
    Mar 2004
    Posts
    205
    delete from test3 from test2 a left join test3 b on a.sno = b.sno where a.sno =2 and b.sno = 2

    In the above left join case , what can I do ?

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    delete from test3 from test2 a left join test3 b on a.sno = b.sno where a.sno =2 and b.sno = 2
    I don't see how the left join makes any sense there. If there is no test3 record matching the test2 record, you have nothing to delete anyway. So may as well use inner join and same approach as I gave before.

    delete from (select * from test2 a, test3 b where b.sno = a.sno and a.sno = 3 and b.sno = 3)
    Yes, that works provided exactly one of the tables is key-preserved in the query, in which case it is the key-preserved table that gets deleted from. A table is key-preserved if Oracle knows that one row in the query always maps one-to-one with one row in the table. If there is a foreign key from test2 referencing test3, then test2 is key-preserved and test3 is not (because the same test3 row may be referenced by many test2 rows). Given that fact, there is no way to make Oracle delete from test3 in that query.

  6. #6
    Join Date
    Mar 2010
    Posts
    3
    Hello,
    i need help, please help me Oracle syntaq
    i want to delete record from t_admin_user_code where t_admin_user inner join a.user = b.user which the condition in the t_admin_user where area_id=5 AND aut='4'

    so i want to dalete from t_admin_user_code where at t_admin_user was area_id=5 AND aut='4'

    thank's

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •