CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
middelpat
middelpat

Reputation: 2585

SQL delete from multiple tables with foreign keys

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?

Upvotes: 4

Views: 7292

Answers (4)

SynerCoder
SynerCoder

Reputation: 12766

(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.

Upvotes: 1

Timothy Khouri
Timothy Khouri

Reputation: 31845

Your design sounds like you could use the CASCADE feature of TSQL. Basically, this will allow you to design your table so that deleting an Upload will always clear out the Orderline as well (or the other way around).

http://msdn.microsoft.com/en-us/library/aa933119(v=SQL.80).aspx

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 134941

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)

Upvotes: 6

Henry
Henry

Reputation: 1008

You need to delete from the child table first then parent.

CREATE TABLE #TEMPTABLE(ID INT)
INSERT INTO #TEMPTABLE(ID)
SELECT UPLOADID FROM ORDERLINES WHERE UPLOADID < 53436;

DELETE FROM ORDERLINES WHERE UPLOADID < 53436;
DELETE FROM UPLOADS WHERE UPLOADID IN (SELECT ID FROM #TEMPTABLE)

DROP TABLE #TEMPTABLE

Upvotes: 1

Related Questions