Deleting records from two tables

im having a very blond day .. carnt get my head round this today
Im rining SQl2005
i have two tables ( A & B ) A contains the 'master' record abd B contains
the 'detail' For every record in A there will be a minimum of 1 record in B
to a max of 1000000
Table A is linked with table B by means of a.LedgerRef = b.LedgerRef
Table A also has a field 'Status'
What i want to do is create a stored procedure that deletes all records in
the Master (A) and Detail(B) tables when A.Status = 'T'
like i said this morning my minds a blanksomething like this?
begin tran
delete from B
from details B
where exists (select 1 from master_tbl A
where a.LedgerRef = b.LedgerRef
and a.status = 't')
delete from master_tbl
where status = 't'
commit tran
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Try this:
USE tempdb
GO
CREATE TABLE A
(
id int,
status char,
LedgerRef int
)
GO
CREATE TABLE B
(
LedgerRef int,
somedata varchar(100)
)
GO
INSERT A VALUES(1, 'T', 10)
INSERT A VALUES(2, 'F', 20)
INSERT B VALUES(10, 'delete it')
INSERT B VALUES(10, 'delete it')
INSERT B VALUES(20, 'don''t delete it')
DELETE B
FROM B JOIN A ON B.LedgerRef = A.LedgerRef
WHERE A.Status = 'T'
DELETE A
WHERE Status = 'T'
Greetings,
Urs
"Peter Newman" wrote:

> im having a very blond day .. carnt get my head round this today
> Im rining SQl2005
> i have two tables ( A & B ) A contains the 'master' record abd B contain
s
> the 'detail' For every record in A there will be a minimum of 1 record in
B
> to a max of 1000000
> Table A is linked with table B by means of a.LedgerRef = b.LedgerRef
> Table A also has a field 'Status'
> What i want to do is create a stored procedure that deletes all records in
> the Master (A) and Detail(B) tables when A.Status = 'T'
> like i said this morning my minds a blank
>|||Use CASCADE DELETE on TableB and you will only have to manage TableA.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:08FA6CBE-88EC-4775-80A1-6413FE99A16A@.microsoft.com...
> im having a very blond day .. carnt get my head round this today
> Im rining SQl2005
> i have two tables ( A & B ) A contains the 'master' record abd B
> contains
> the 'detail' For every record in A there will be a minimum of 1 record in
> B
> to a max of 1000000
> Table A is linked with table B by means of a.LedgerRef = b.LedgerRef
> Table A also has a field 'Status'
> What i want to do is create a stored procedure that deletes all records in
> the Master (A) and Detail(B) tables when A.Status = 'T'
> like i said this morning my minds a blank
>