April 7, 2011 at 9:49 am
I have the following tables;
1. document (it has the following columns docID, containerID, userID)
2. documentProp (it has the following columns - docID,versionID)
3. documentVersion (it has the following columns - docID, versionID)
4. documentBodyVersion(it has the following colums - bodyID, docID, versionID)
My task is the delete all the documents (via their docID) which are in a container (via their containerID)
per my code below....
delete d, dp, dv, dbv
from document as d
left join jocumentProp as dp on dp.docID = d.docID
left join docVersion as dv on dv.docID = dp.docID
left join docBodyVersion as dbv on dbv.docID = dv.docID
where d.containerID = '12345'
but I keep getting the error -
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
April 7, 2011 at 9:54 am
You could try this, but i haven't tested it:
delete d.*, dp.*, dv.*, dbv.*
from document as d
left join jocumentProp as dp on dp.docID = d.docID
left join docVersion as dv on dv.docID = dp.docID
left join docBodyVersion as dbv on dbv.docID = dv.docID
where d.containerID = '12345'
April 7, 2011 at 9:59 am
DELETE can only delete rows from one table so you will need to delete from each table in the correct order.
Something like the following:
DELETE docBodyVersion
WHERE EXISTS
(
SELECT *
FROM document D
WHERE D.docID = docBodyVersion.docID
AND D.containerID = '12345'
)
DELETE documentProp
WHERE EXISTS
(
SELECT *
FROM document D
WHERE D.docID = documentProp.docID
AND D.containerID = '12345'
)
DELETE documentVersion
WHERE EXISTS
(
SELECT *
FROM document D
WHERE D.docID = documentVersion.docID
AND D.containerID = '12345'
)
DELETE document
WHERE containerID = '12345'
April 7, 2011 at 1:24 pm
Or ...
DECLARE @Docs TABLE (DocId Int)
INSERT INTO @Docs(docId)
SELECT d.docId
from [document] as d
where d.containerID = '12345'
DELETE d
FROM [document] as d
INNER JOIN @Docs del
ON d.docId = del.docId
DELETE d
FROM jocumentProp as d
INNER JOIN @Docs del
ON d.docId = del.docId
DELETE d
FROM docVersion as d
INNER JOIN @Docs del
ON d.docId = del.docId
DELETE d
FROM docBodyVersion as d
INNER JOIN @Docs del
ON d.docId = del.docId
April 7, 2011 at 2:35 pm
Ken McKelvey (4/7/2011)
DELETE can only delete rows from one table so you will need to delete from each table in the correct order.Something like the following:
DELETE docBodyVersion
WHERE EXISTS
(
SELECT *
FROM document D
WHERE D.docID = docBodyVersion.docID
AND D.containerID = '12345'
)
DELETE documentProp
WHERE EXISTS
(
SELECT *
FROM document D
WHERE D.docID = documentProp.docID
AND D.containerID = '12345'
)
DELETE documentVersion
WHERE EXISTS
(
SELECT *
FROM document D
WHERE D.docID = documentVersion.docID
AND D.containerID = '12345'
)
DELETE document
WHERE containerID = '12345'
Thanks - this works perfectly!
April 7, 2011 at 2:52 pm
Hi ,
Have look on this
CREATE TABLE #MyTable (My int)
CREATE TABLE #MyTable1 (My int)
CREATE TABLE #MyTable2 (My int)
INSERT INTO #MyTable
SELECT 65 UNION ALL
SELECT 54 UNION ALL
SELECT 46
INSERT INTO #MyTable1
SELECT 46 UNION ALL
SELECT 54 UNION ALL
SELECT 65
INSERT INTO #MyTable2
SELECT 46 UNION ALL
SELECT 45 UNION ALL
SELECT 23
DELETE #MyTable1
WHERE EXISTS
(SELECT * FROM #MyTable D WHERE D.My = #MyTable1.My AND D.My = '46'
)
DELETE #MyTable2
WHERE EXISTS
(SELECT * FROM #MyTable D WHERE D.My = #MyTable2.My AND D.My = '46'
)
Select *from #MyTable
Select *from #MyTable1
Select *from #MyTable2
DROP TABLE #MyTable,#MyTable1,#MyTable2
You can do DROP with "," comma but not the DELETE with "," comma.
In your case you can use CASCADE on DELETE optition since all are having REFERENCES .
Thanks
Parthi
April 7, 2011 at 3:28 pm
rabisco (4/7/2011)
Ken McKelvey (4/7/2011)
DELETE can only delete rows from one table so you will need to delete from each table in the correct order.Something like the following:
DELETE docBodyVersion
WHERE EXISTS
(
SELECT *
FROM document D
WHERE D.docID = docBodyVersion.docID
AND D.containerID = '12345'
)
DELETE documentProp
WHERE EXISTS
(
SELECT *
FROM document D
WHERE D.docID = documentProp.docID
AND D.containerID = '12345'
)
DELETE documentVersion
WHERE EXISTS
(
SELECT *
FROM document D
WHERE D.docID = documentVersion.docID
AND D.containerID = '12345'
)
DELETE document
WHERE containerID = '12345'
Thanks - this works perfectly!
I would recommend that you put this all inside a transaction and build in some error checking. If the first delete works, but the second fails - for whatever reason, you would want to rollback the transaction.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply