1

I want to do this:

delete from table1 a,table2 b, table3  c 
 where a.col1 = b.col1 
   and b.col2 = c.col2 
   and a.co3 <> 8001;

But its giving me an error.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
SmartestVEGA
  • 8,415
  • 26
  • 86
  • 139

4 Answers4

3

delete the lowest level first and move up from there, one delete per level, to the highest level:

DELETE FROM ChildTable WHERE ParentID=...

DELECT FROM ParentTable WHERE ParentID=...
KM.
  • 101,727
  • 34
  • 178
  • 212
2

You could turn cascade deletes on then delete the parent record.

bkaid
  • 51,465
  • 22
  • 112
  • 128
  • +1 I was going to write exact same answer. That is the only automated way of deleting related records. Other option was depicted in the first answer. – mevdiven Mar 09 '10 at 15:48
  • 1
    Being explicit is the *best* answer. `CASCADE ON DELETE` can cause problems if someone absent-mindedly triggers it, and SQL Server 2005 `CASCADE ON DELETE` can be applied but won't work when it's a self-referencial relationship. – OMG Ponies Mar 09 '10 at 15:49
  • Valid point OMG Ponies but also there are often times though when deleting a parent record it wouldn't make sense to keep the child records at all and casade on delete makes sense. – bkaid Mar 09 '10 at 15:57
1

Since you did not specify to what each table has a foreign key and on which field, I'll take a guess:

Delete TableC
Where Exists( Select 1 From TableA Where TableA.Col1 = TableC.Col2 And TableA.Col3 <> '8001' )

Delete TableB
Where Exists( Select 1 From TableA Where TableA.Col1 = TableB.Col2 And TableA.Col3 <> '8001' )

Delete TableA
Where Col3 <> '8001'
Thomas
  • 63,911
  • 12
  • 95
  • 141
0

delete A from table1 a,table2 b, table3 c
where a.col1 = b.col1
and b.col2 = c.col2
and a.co3 <> 8001;

anu
  • 1