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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy