How to delete junk customer records using stored procedure.

glhays wrote:
(almost*) All tables have an column named Id (and have the IDENTITY property).  For foreign key relationships, the naming convention for columns is to prefix the name with the refers-to table name - e.g. the CustomerId column in the ActivityLog table is the Id in the Customer table.

(*there are some exceptions; like the 'mapping' tables - e.g. Customer_CustomerRole_Mapping.  And also there, they seemed to have left the legacy/prior naming convention - e.g. Customer_Id , with underscore.)

I have been receiving a lot of spam, This query works great, but how to delete the selection:

SELECT c.[Id],[Username],[Email],c.[CreatedOnUtc],[LastLoginDateUtc],[LastActivityDateUtc]
FROM [Customer] as c
join Customer_CustomerRole_Mapping as crm on c.id=crm.Customer_Id
left join ShoppingCartItem as sc on c.id=sc.CustomerId
left join CustomerAddresses as ca on c.id=ca.Customer_Id
where  CustomerRole_Id =3 and c.deleted=0
and ca.Address_Id is null
and sc.CustomerId is null
and datediff(day, c.CreatedOnUtc, LastActivityDateUtc) = 0
order by c.Id, LastLoginDateUtc, LastActivityDateUtc

Thank you!

Two ways
a) wrap SELECT with a WITH -

WITH del as
(
   ...your entire SELECT statement as-is with the ORDER BY commented out
)
DELETE FROM Customer where Id in (select Id from del)

or  remove SELECT / change to DELETE
b)

DELETE Customer
FROM [Customer] as c
... and the remainder of your SQL with the ORDER BY commented out ...


(In any case, it's advised that you make a backup prior to any mass SQL updates ;)

Thanks NY, can you post the complete query to delete the spam clients, I'm not a SQL savvy.

First, I modified the query just a bit, because
a) I put in comments that indicate what each clause is doing
b) The original poster mentioned "and no orders", but I don't see that they checked that explicitly.  Maybe they assumed if the customer did not have an Address, but I just want to be sure.  (e.g. free digital order??)
c) I added a clause where you can adjust the number of days ago (old).  Because you may want to allow for a recent registered customer to come back within a couple of days.  I made it "2", but you can change to "0" if you like
d) also added IP Address and DaysAgo to the SELECT

SELECT c.[Id],[Username],[Email],c.[CreatedOnUtc],[LastLoginDateUtc],[LastActivityDateUtc]
     , c.LastIpAddress
     , datediff(day, c.CreatedOnUtc, GetDate())  as DaysAgo
FROM [Customer] as c
join Customer_CustomerRole_Mapping as crm on c.id=crm.Customer_Id
left join ShoppingCartItem  as sc on c.id=sc.CustomerId
left join CustomerAddresses as ca on c.id=ca.Customer_Id
left join [Order]           as o  on c.id=o.CustomerId
where CustomerRole_Id =3    --registered
and c.deleted=0             --not deleted
and ca.Address_Id is null   --no addresses
and sc.CustomerId is null   --no shopping cart items
and o.CustomerId is null    --no orders
and datediff(day, c.CreatedOnUtc, LastActivityDateUtc) = 0  --last activity was on same day as create
and datediff(day, c.CreatedOnUtc, GetDate()) > 2            --how many days ago that they were created
order by c.Id, LastLoginDateUtc, LastActivityDateUtc

So, to make that a a DELETE, change SELECT to "DELETE Customer", and remove the "order by" clause:

DELETE Customer
FROM [Customer] as c
join Customer_CustomerRole_Mapping as crm on c.id=crm.Customer_Id
left join ShoppingCartItem  as sc on c.id=sc.CustomerId
left join CustomerAddresses as ca on c.id=ca.Customer_Id
left join [Order]           as o  on c.id=o.CustomerId
where CustomerRole_Id =3    --registered
and c.deleted=0             --not deleted
and ca.Address_Id is null   --no addresses
and sc.CustomerId is null   --no shopping cart items
and o.CustomerId is null    --no orders
and datediff(day, c.CreatedOnUtc, LastActivityDateUtc) = 0  --last activity was on same day as create
and datediff(day, c.CreatedOnUtc, GetDate()) > 2            --how many days ago that they were created

Thank you NY, will help a lot of people with the same issue!
Thanks again!