0

I'm trying to delete from two tables with only 1 query.

I've tried these:

 Dim cq As String = "delete c.*, sb.* from Table1 c 
                     left join Table2 sb on c.ID = sb.OrderID 
                     where c.ID=" & txtID2.Text & ""

but I get "Record is deleted."

 Dim cq As String = "delete t1,t2 from Table1 as t1 
                     join Table2 as t2 on t2.OrderID = t1.ID 
                     where ID=" & txtID2.Text & ""

but I get "Syntax error in from clause."

Kindly fix my code or teach me regarding this. I'd be very grateful.

user3522506
  • 55
  • 7
  • 1
    Have you looked at this? http://stackoverflow.com/a/1233479/2638872 – mrtig Aug 21 '14 at 00:47
  • 2
    If the two tables have a referential integrity constraint (parent-child relationship), you can setup cascading deletes. If you delete the parent, the children are automatically deleted. It makes things much simpler and more robust. – Steve Wellens Aug 21 '14 at 00:50
  • @mrtig Yes, I have. My first attempt actually. Still the same error "Syntax error in FROM clause". – user3522506 Aug 21 '14 at 01:17
  • @SteveWellens Thanks for the suggestion. Actually I'm trying to experiment on this just to practice initially deleting with joins this way. :D – user3522506 Aug 21 '14 at 01:19
  • 1
    One other thing... by building the string from a web-based variable, you are wide open to SQL-injection... Use OleDbParameter() for your queries. – DRapp Aug 21 '14 at 03:18
  • @DRapp no problem. It's a Window-based app but yes I would do that. Thanks! – user3522506 Aug 21 '14 at 04:05

0 Answers0