February 4, 2011 at 2:06 pm
I am working on a C# app and I am new to this company's data and processes. I need to delete these records to retest the c# front end app.
delete a,b,c
from tblSubmissions a join tblSubmissionHistory b
on a.submissionID = b.submissionID
join tblsubmissionBatch c
on b.submissionbatchID = c.submissionbatchID
where c.submissionbatchID = 105 and b.createdby = 'aspencer' and b.CreatedOn = '2011-02-04 12:32:44.410'
I am basing this delete off this working select below:
select a
from tblSubmissions a join tblSubmissionHistory b
on a.submissionID = b.submissionID
join tblsubmissionBatch c
on b.submissionbatchID = c.submissionbatchID
where c.submissionbatchID = 105 and b.createdby = 'aspencer' and b.CreatedOn = '2011-02-04 12:32:44.410'
Also this is the only way my select's where clause above worked, I did not want to put in the seconds in the b.CreatedOn column, just up to the day. But could not get it to work any other way.
Thanks in advance.
February 4, 2011 at 2:10 pm
Delete only works on one table at a time (Select is really the only one that can work with multiple target tables).
What you'll need to do is delete from "a", then from "b", then from "c". It's probably a matter of just breaking down the query a bit, but I'd need to know a bit more about the criteria for the deletion before I could state that definitively.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 4, 2011 at 2:20 pm
Well I started with table a with the plan on doing them one at a time. Then I replaced a with b then c using the same delete and all is gone from all. like this:
Did I hose the DEV tables or did it work?
delete a
from tblSubmissions a join tblSubmissionHistory b
on a.submissionID = b.submissionID
join tblsubmissionBatch c
on b.submissionbatchID = c.submissionbatchID
where c.submissionbatchID = 105 and b.createdby = 'aspencer' and b.CreatedOn = '2011-02-04 12:32:44.410' -- 5027 rows deleted
delete b
from tblSubmissions a join tblSubmissionHistory b
on a.submissionID = b.submissionID
join tblsubmissionBatch c
on b.submissionbatchID = c.submissionbatchID
where c.submissionbatchID = 105 and b.createdby = 'aspencer' and b.CreatedOn = '2011-02-04 12:32:44.410'--0 rows deleted
delete c
from tblSubmissions a join tblSubmissionHistory b
on a.submissionID = b.submissionID
join tblsubmissionBatch c
on b.submissionbatchID = c.submissionbatchID
where c.submissionbatchID = 105 and b.createdby = 'aspencer' and b.CreatedOn = '2011-02-04 12:32:44.410' --0 rows deleted
February 5, 2011 at 3:14 pm
Well, since the first statement deletes from a, and the second and third depend on the data still being in a for their joins to work, I'd be surprised if the last two actually deleted anything.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 7, 2011 at 1:42 pm
table A has a cascading delete to Table B if found.
I figured it out!
February 7, 2011 at 1:48 pm
Cool biz.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply