• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

Delete all record belong to inactive users

Hi,

I want to delete all record that belong to the inactive users.

I have first selected all the inactive usersID and stored them in array
than I need to loop thru the array and delete all ads belong the users in the 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#")> 
</cfloop> 

<CFQUERY DATASOURCE="xx" NAME="DeleteRecordBelongToInactiveUser" cachedwithin="#createTimeSpan(0,0,0,-1)#">
<cfloop from="1" to="#ArrayLen(myArray)#" index="i">
Delete from ADS
where datediff(NOW(),AdsDate) > 365 and UserID = (myArray,i)>
</cfloop>
</CFQUERY>

</cfif>

Open in new window

0
lulu50
Asked:
lulu50
  • 5
  • 3
  • 2
1 Solution
 
Brijesh ChauhanStaff IT EngineerCommented:
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>

Open in new window

0
 
gdemariaCommented:
It would be most efficient to do it all in one statement..   this can replace all the code you have, no looping, no array needed...


<CFQUERY DATASOURCE="xx" NAME="DeleteRecordBelongToInactiveUser" cachedwithin="#createTimeSpan(0,0,0,-1)#">
  Delete from ADS
  where datediff(NOW(),AdsDate) > 365
  and UserID in (select UserTbl.UserID
     from UserTbl
     where UserTbl.UserStatus = "I")
</CFQUERY>

0
 
gdemariaCommented:

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)

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Brijesh ChauhanStaff IT EngineerCommented:
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 ...
0
 
Brijesh ChauhanStaff IT EngineerCommented:
For SQL Server

DELETE FROM ADS
WHERE EXISTS
  ( select UserTbl.UserID,UserTbl.UserStatus from UserTbl
      where UserTbl.UserStatus = "I");
      and datediff(NOW(),AdsDate) > 365
0
 
Brijesh ChauhanStaff IT EngineerCommented:
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>

Open in new window

     
0
 
gdemariaCommented:
>  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

0
 
lulu50Author Commented:
I am using MySql database
0
 
Brijesh ChauhanStaff IT EngineerCommented:
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
0
 
lulu50Author Commented:
Thanks
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now