0

I have table1 with columns:

def_id, def_name, username etc

I have another table table2 which has some association:

assoc_id,parent_id,child_id

The parent_id , child_id are actually def_id's from Table1 . They get inserted into Table2 based on parent_child relation user action in GUI.

Now I want to select all def_id for a particular username from Table1 and then use that input to delete all the records if those def_ids are part of the parent_id or child_id from Table2.

How do I do this in SQL? I am using Sybase database.

Any help will be appreciated

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Chandu
  • 1,049
  • 3
  • 11
  • 18

3 Answers3

2
 Delete Table2
 Where parent_id In
     (Select def_id from table1
      Where username = @username) Or
     child_id In
     (Select def_id from table1
      Where username = @username)

Or

  Delete t2
  From table2 t2
  Where Exists
     (Select * From Table1
      Where def_id In 
          (t2.parent_Id, t2.child_Id))
Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
  • Thanks all for the replies. Really thankfull for helping me out. Charles Bretana's first answer did the trick for me, the next query he suggested gave me a Syntax error SQL Anywhere Error -131: Syntax error near 'Where' on line 1 Thanks -C – Chandu Sep 22 '11 at 17:55
  • You may need to add a from clause to get the second one to work... I edited to show... – Charles Bretana Sep 22 '11 at 19:45
0

An easy way is adding a subquery to the where clause.

DELETE ab
FROM AuthorArticle AS ab
WHERE ab.AuthID=(SELECT AuthID FROM Authors WHERE AuthorLastName='Yin')

Never used Sybase, but this is basic SQL and should work.

Caimen
  • 2,623
  • 2
  • 26
  • 43
0

Try:

DELETE table2
FROM table2
INNER JOIN table1 ON table1.def_id IN (table2.parent_id, table2.child_id)
kevev22
  • 3,737
  • 21
  • 32