Delete all record belong to inactive users

Lulu.. which database are you using ?

You can write the delete query in the first loop itself where you are creating the userIDInactive array...

<!---For service, and community type of post will be deleted after 12 months if the user is inactive--->
<CFQUERY DATASOURCE="xx" NAME="GetStatusUser" cachedwithin="#createTimeSpan(0,0,0,-1)#">
select UserTbl.UserID,UserTbl.UserStatus from UserTbl
where UserTbl.UserStatus = "I"
</CFQUERY>

<cfif isDefined("GetStatusUser") and GetStatusUser.recordcount gt 0>

<cfset myArray = ArrayNew(1)> 
<cfloop query = "GetStatusUser">
	<cfset UserIDInactive = ArrayAppend(myArray,"#UserID#")> 
	<CFQUERY DATASOURCE="xx" NAME="DeleteRecordBelongToInactiveUser" cachedwithin="#createTimeSpan(0,0,0,-1)#">
		delete from ADS
		where datediff(NOW(),AdsDate) > 365 and UserID = '#UserID#'
 	</CFQUERY>
</cfloop> 

</cfif>

Select allOpen in new window

By the way, I see you're using NOW() in the where clause.  That's a Coldfusion function, it could also be for your database depending on which you're using (MS Access?)

But for SQL Server, you would use  getDate()

And for Oracle it would be something like sysdate  (can't quite remember)

Yes, you can write on statement, but some databases, like oracle has a restriction of 1000 records that you can put with IN (), so that is why I asked what database is the author using..

For oracle it is SYSDATE ...

For SQL Server

DELETE FROM ADS
WHERE EXISTS
  ( select UserTbl.UserID,UserTbl.UserStatus from UserTbl
      where UserTbl.UserStatus = "I");
      and datediff(NOW(),AdsDate) > 365

I think you are using SQL server, if yes, then here is the single query which you can try...

	<CFQUERY DATASOURCE="xx" NAME="DeleteRecordBelongToInactiveUser" cachedwithin="#createTimeSpan(0,0,0,-1)#">
		DELETE FROM ADS
		WHERE EXISTS
  		(SELECT UserTbl.UserID,UserTbl.UserStatus from UserTbl
      	where UserTbl.UserStatus = "I")
      	and datediff(day,getDate(),AdsDate) > 365 
 	</CFQUERY>

Select allOpen in new window

     

>  Yes, you can write on statement, but some databases, like oracle has a restriction of 1000 records that you can put with IN ()

Note that this restriction is when listing out elements such as  in (1,2,3,4,5,6...)   It does not apply when using  a subquery such as this one... there is no limit to this

 and UserID in (select UserTbl.UserID
     from UserTbl
     where UserTbl.UserStatus = "I")


You can of course also use EXISTS, however, this statement will delete all records in ADS based on the date and ignore the user's status.   That is because there is no association between the ADS table and the sub query...


  DELETE FROM ADS
  WHERE EXISTS
     (SELECT UserTbl.UserID,UserTbl.UserStatus from UserTbl
         where UserTbl.UserStatus = "I")
   and datediff(day,getDate(),AdsDate) > 365

You need to add a join in the sub query like this...


  DELETE FROM ADS
  WHERE EXISTS
      (select 1     ------ no need to select columns
         from UserTbl
         where UserTbl.UserStatus = "I"
         and     UserTbl.UserID  = ads.userID   ------- this join is needed
         )
   and datediff(day,getDate(),AdsDate) > 365

I am using MySql database

For mysql, you can use IN statement as suggested by GD and NOW() is a function in MySQL as well so your datediff statement is correct..

      Delete from ADS where userID in (select UserTbl.UserID,UserTbl.UserStatus from UserTbl
      where UserTbl.UserStatus = "I")
      and datediff(NOW(),AdsDate) > 365

Thanks