sql - Using INNER JOIN in DELETE -

i'm trying following condition: if loan in loans table has outstandingamount < 0, delete relevant information in database. have in single command , hence, have tried using inner join:

delete a, b, c, d, e loans t1 inner join payments t2 on t1.loanid = t2.loanid inner join repayments t3 on t1.loanid = t3.loanid inner join histories t4 on t1.loanid = t4.loanid inner join loanrequests t5 on t1.requestdate = t5.requestdate , t1.bid = t5.bid inner join commits t6 on t1.requestdate = t6.requestdate , t1.bid = t6.bid t1.outstandingamount < 0 

however, command gives me syntax error @ "delete a, b," , i'm not sure work. appreciated. thank you.

as gordon linoff wrote in comment, can delete 1 table in each delete statement.

you have 2 options:

1. use on delete cascade in foreign keys (that's best thing do)

2. use delete statement each table, wrap entire delete process in transaction.

adding on delete cascade foreign keys means drop , re-create them:

alter table dbo.loans drop constraint fk_loans_payments;   alter table dbo.loans add constraint fk_loans_payments foreign key (loanid) references payments(loanid) on delete cascade;  

using transaction wrap individual delete statements:

begin trasaction  begin try  delete c commits  inner join loans l on l.requestdate = c.requestdate  l.outstandingamount < 0  delete lr loanrequests lr inner join loans l on l.requestdate = lr.requestdate  l.outstandingamount < 0  -- more of same...  delete  loans outstandingamount < 0  commit transaction  end try begin catch      if @@trancount > 0         rollback transaction end catch