Deleting records from two tables

30 Jun 2006 8:04 AM

Peter Newman

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

30 Jun 2006 8:29 AM

Omnibuzz

something 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/

30 Jun 2006 9:03 AM

Urs

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

Show quote

"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 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
>

30 Jun 2006 3:21 PM

Arnie Rowland

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

Show quote

"Peter Newman" <PeterNew***@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
>