SQL delete from multiple tables with foreign keys

Question

I have 2 tables which are connected by foreign keys, the fields UploadID.

I want to delete some rows from the database. I tried several SQL queries which won't work.

Now I have this one which I think should do the trick but is get the error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

DELETE 
  a, b FROM [Uploads] as a, 
  [OrderLines] as b 
  WHERE [Uploads].UploadID < 53436;

any thoughts?

Answer 1

You can only delete from 1 table at a time in SQL Server

However if you have your keys setup with cascade delete then if you delete them from the parent table, then they will be deleted automatically from the child tables

otherwise you need to run two delete statements, first one for the child rows, then one for the parent rows

something like this...however I am puzzled..where is your JOIN condition?

DELETE 
  b FROM [Uploads] as a, 
  [OrderLines] as b 
  WHERE [Uploads].UploadID < 53436;

DELETE 
  [Uploads] 
  WHERE [Uploads].UploadID < 53436;

I would prefer to use a new style ANSI JOIN..like the following

DELETE 
  b FROM [Uploads] as a, 
  JOIN [OrderLines] as b on A.SomeColumns = b.SomeColumn
  WHERE a.UploadID < 53436;

you pretty much have a cartesian product(cross join)

Answer 2

(First off let me say I know the DB)

I know you want to delete the old uploads. Instead of doing so,, rewrite the C#/asp.NET code to show only uploads that are younger then 40 days.