Stored Procedure to Delete Rows

20 Jan 2006 11:38 AM

Dooza

Hi there,
I have made this statement that will select credit card details from one
table if the order status is shipped in another table.

SELECT     dbo.tblOrderDetails.orderStatus,
dbo.tblCreditCardDetailsTest.orderNumer, dbo.tblOrderDetails.cName,
                       dbo.tblCreditCardDetailsTest.creditCardNumber
FROM         dbo.tblCreditCardDetailsTest INNER JOIN
                       dbo.tblOrderDetails ON
dbo.tblCreditCardDetailsTest.orderNumer = dbo.tblOrderDetails.orderNumber
WHERE     (dbo.tblOrderDetails.orderStatus = N'SHIPPED')

I now want to make this into a delete statement, but I dont know how.
Can anyone help?

Cheers,

Steve

20 Jan 2006 11:47 AM

Ryan

BEGIN TRAN

DELETE tblCreditCardDetailsTest where orderNumer IN

( SELECT   dbo.tblCreditCardDetailsTest.orderNumer FROM
dbo.tblCreditCardDetailsTest INNER JOIN
                       dbo.tblOrderDetails ON
dbo.tblCreditCardDetailsTest.orderNumer = dbo.tblOrderDetails.orderNumber
WHERE     (dbo.tblOrderDetails.orderStatus = N'SHIPPED'))

only COMMIT TRAN if the rows affected is what you expected..

--
HTH. Ryan

Show quote

"Dooza" <steve@dont.spam.me.dooza.tv> wrote in message
news:eA2zQYbHGHA.2696@TK2MSFTNGP14.phx.gbl...
> Hi there,
> I have made this statement that will select credit card details from one
> table if the order status is shipped in another table.
>
> SELECT     dbo.tblOrderDetails.orderStatus,
> dbo.tblCreditCardDetailsTest.orderNumer, dbo.tblOrderDetails.cName,
>                       dbo.tblCreditCardDetailsTest.creditCardNumber
> FROM         dbo.tblCreditCardDetailsTest INNER JOIN
>                       dbo.tblOrderDetails ON
> dbo.tblCreditCardDetailsTest.orderNumer = dbo.tblOrderDetails.orderNumber
> WHERE     (dbo.tblOrderDetails.orderStatus = N'SHIPPED')
>
> I now want to make this into a delete statement, but I dont know how. Can
> anyone help?
>
> Cheers,
>
> Steve

20 Jan 2006 12:42 PM

Dooza

Ryan wrote:

> BEGIN TRAN
>
> DELETE tblCreditCardDetailsTest where orderNumer IN
>
> ( SELECT   dbo.tblCreditCardDetailsTest.orderNumer FROM
> dbo.tblCreditCardDetailsTest INNER JOIN
>                        dbo.tblOrderDetails ON
> dbo.tblCreditCardDetailsTest.orderNumer = dbo.tblOrderDetails.orderNumber
> WHERE     (dbo.tblOrderDetails.orderStatus = N'SHIPPED'))
>
>
> only COMMIT TRAN if the rows affected is what you expected..
>

Hi Ryan,
That was perfect! Thank you very much indeed. I will remember this
technique, as I am sure it will be usefull for other things.

Steve

20 Jan 2006 12:48 PM

Ryan

No problem, if i'm honest this isn't a very effecient way of going about it.
The EXISTS condition would achevie the same with less overhead. If you wish
to familiarise yourself have a look at this :-

http://www.techonthenet.com/sql/exists.php

--
HTH. Ryan

Show quote

"Dooza" <steve@dont.spam.me.dooza.tv> wrote in message
news:u5Nx97bHGHA.2704@TK2MSFTNGP15.phx.gbl...
> Ryan wrote:
>> BEGIN TRAN
>>
>> DELETE tblCreditCardDetailsTest where orderNumer IN
>>
>> ( SELECT   dbo.tblCreditCardDetailsTest.orderNumer FROM
>> dbo.tblCreditCardDetailsTest INNER JOIN
>>                        dbo.tblOrderDetails ON
>> dbo.tblCreditCardDetailsTest.orderNumer =
>> dbo.tblOrderDetails.orderNumber
>> WHERE     (dbo.tblOrderDetails.orderStatus = N'SHIPPED'))
>>
>>
>> only COMMIT TRAN if the rows affected is what you expected..
>>
>
> Hi Ryan,
> That was perfect! Thank you very much indeed. I will remember this
> technique, as I am sure it will be usefull for other things.
>
> Steve