Deleting Multiple Rows Using CFquery

<cfquery name="deletePurchaseInfo" datasource="masterhub" dbtype="odbc">
    DELETE FROM wsPurchaseInfo
    WHERE a.PURINFO_PURCHASE_ID in ( select PURCHASE_ID    
                                   FROM wsPurchase
                                   WHERE PURCHASE_MAS_NO  = '#form.MAS_NO#'
)

</cfquery>

let me know if there is any problem

regards,
Dash

there should any a.PURINFO_PURCHASE_ID

<cfquery name="deletePurchaseInfo" datasource="masterhub" dbtype="odbc">
   DELETE FROM wsPurchaseInfo
   WHERE PURINFO_PURCHASE_ID in ( select PURCHASE_ID    
                                  FROM wsPurchase
                                  WHERE PURCHASE_MAS_NO  = '#form.MAS_NO#'
)

</cfquery>

all the above are right , but..
i would go with anand's, just because an IN operator may not be the best choice if the subquery is to return a large number of rows...

best wishes..
-r

<cfquery name="deletePurchaseInfo" datasource="masterhub">
delete wsPurchaseInfo
from wsPurchaseInfo inner join wsPurchase
  on wsPurchaseInfo.PURCHASE_ID= wsPurchase.PURCHASE_ID
  and wsPurchase.PURCHASE_MAS_NO = '#trim(form.MAS_NO)#'
</cfquery>

hi raj,

do u have any document where it mention "IN operator may not be the best choice if the subquery is to return a large number of rows..." or it is yours.

i never said my comment is optimum.

regards,
Dash

lol Dash, sorry to hurt ur sentimnents- no offense intended, i was just expressing my $0.02 ;-)

A priliminary look at the google user group for sql revealed the following explanation by Joe Celko (who is considered as a sql guru by all in that UG)

"Because the optimizer is not smart enough to "flatten" the query into a JOIN.  Instead it builds the table described in the subquery, then removes redundant duplicate rows from it, and finally compares those rows to the outer table reference.  That is what the  additional "Sort/Distinct Sort" on the result of the subquery means."

(note this is pertaining to sql server, am sure holds water in any rdbms)

cheers
-r

it is ok.
i know oracle, IN operation in subquery will not used the index on the table. that the reason to make it fast we need to do in exist clause

like
instead of

select * from
table
where id in  ( select id from table2)
   
we can use

select * from
table
where exists ( select id from table2)
   

i learnt the hard way, i had a subquery which looked innocently simple and straight-forward, but as the number of records ballooned in the database, my subquery also swelled and drastically affected my page performance.. oncei redid the entire query with using JOINS - boy- she kicked ass !!

cheers
-r

Hi. Thank you all for helping. It's interesting to get so many different kinda ways to solve a problem. However I decided to award the points to anand because his answer is the most straight-forward. Thanks again.