Cleaning up SQL database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cleaning up SQL database

Author

Topic

noonz
Starting Member

33 Posts

Posted - 2010-07-22 : 11:19:53

Hello all!

I have a messageboard that runs on SQL 2005.

There are thousands of inactive users, and spam users which I would delete.

However, my problem is that they have user data in other tables.

cs_Users

aspnet_Membership

aspnet_Users

They have a common value, such as the "UserID" column.

There is a column in the cs_Users table, "LastActivity"

I want to delete these users who haven't had any activity this year, ">=01-01-2010"

How can I do a simultaneous removal of these users and make sure everything is removed from the three tables at once?

I appreciate it everyone!!

Mike

Kristen
Test

22859 Posts

Posted - 2010-07-22 : 11:47:04

If this is a oneoff I tend to do something like this:


SELECT TOP 100 Col1, Col2, ... --- Display for testing purposes only
-- DELETE D4
-- DELETE D3
-- DELETE D2
-- DELETE D1
-- SELECT COUNT(*)
FROM MasterTable AS D4
     LEFT OUTER JOIN ChildTable AS D2
         ON D2.UserName = D4.UserName
     LEFT OUTER JOIN GrandChildTable AS D1
         ON D1.SomeID = D2.SomeID
     LEFT OUTER JOIN SiblingTable AS D3
         ON D3.UserName = D4.UserName
WHERE D4.lastActivity < '20100101'

I can run the SELECT to see representative data - to check that what I'm going to delete is sensible

I can run the SELECT COUNT(*) to see how many rows are involved

Then I can SELECT from DELETE D1 to end to delete the first (grand-child) batch, then repeat highlighting from DELETE D2 to the end, and so on.

I would execute a BEGIN TRANSACTION before I started, and if I got any errors I would execute a ROLLBACK, if there were no errors I would execute COMMIT at the end.

noonz
Starting Member

33 Posts

Posted - 2010-07-22 : 11:52:40

Kristen,

With all do respect, I am confused.

noonz
Starting Member

33 Posts

Posted - 2010-07-22 : 11:56:30

Col1...Col2....

Which table do I have to specify all columns on? And do I need to specify them all?

Kristen
Test

22859 Posts

Posted - 2010-07-22 : 12:04:00

Any that you want to "review" before launching the deletes - just as a check that all is well.

In SSMS if you highlight part of a statement and press EXECUTE only the highlighted part will run. The DELETES are commented out (by the "--" in front of them), so will be ignored when you run the main SELECT. You can then highlight from the start of "DELETE D1 ..." to the end, and press execute, to delete that block

Alternatively


SELECT Username
INTO #MyTempTable
FROM MasterTable
WHERE D4.lastActivity < '20100101'

DELETE D1
FROM #MyTempTable AS T
     JOIN MasterTable AS D4
         ON D4.UserName = T.UserName
     LEFT OUTER JOIN ChildTable AS D2
         ON D2.UserName = D4.UserName
     LEFT OUTER JOIN GrandChildTable AS D1
         ON D1.SomeID = D2.SomeID
     LEFT OUTER JOIN SiblingTable AS D3
         ON D3.UserName = D4.UserName


DELETE D2
FROM #MyTempTable AS T
     JOIN MasterTable AS D4
         ON D4.UserName = T.UserName
     LEFT OUTER JOIN ChildTable AS D2
         ON D2.UserName = D4.UserName
     LEFT OUTER JOIN GrandChildTable AS D1
         ON D1.SomeID = D2.SomeID
     LEFT OUTER JOIN SiblingTable AS D3
         ON D3.UserName = D4.UserName
... etc.

the important part being that the code is identical for all the DELETEs so that no bugs creep in from accidental variations

noonz
Starting Member

33 Posts

Posted - 2010-07-22 : 12:15:33

Here is what I did:

SELECT TOP 100 MembershipID, ForceLogin, UserAccountStatus, AppUserToken, LastActivity, LastAction
FROM cs_Users AS D4
LEFT OUTER JOIN aspnet_Users AS D2
ON D2.UserId = D4.MembershipID
LEFT OUTER JOIN aspnet_Membership AS D1
ON D1.UserId = D2.UserId
WHERE D4.LastActivity >= '2010-01-01 12:00:00.000'
ORDER BY D4.LastActivity

So this returns the joined tables as values for which I specified?

Will these "DELETE D"# queries delete the entire row? Or only the row values for the columns I have specified?

I need to wipe the entire row for these in all of the tables...

noonz
Starting Member

33 Posts

Posted - 2010-07-22 : 12:19:33

Correction:

SELECT TOP 100 *
FROM cs_Users AS D4
LEFT OUTER JOIN aspnet_Users AS D2
ON D2.UserId = D4.MembershipID
LEFT OUTER JOIN aspnet_Membership AS D1
ON D1.UserId = D2.UserId
WHERE D4.LastActivity >= '2010-01-01 12:00:00.000'
ORDER BY D4.LastActivity

noonz
Starting Member

33 Posts

Posted - 2010-07-22 : 13:05:37

SELECT * FROM cs_Users AS D4
DELETE D4
--DELETE D2
--DELETE D1
FROM cs_Users AS T
JOIN cs_Users as D4
ON D4.MembershipId = T.MembershipId
LEFT OUTER JOIN aspnet_Users AS D2
ON D2.UserId = D4.MembershipId
LEFT OUTER JOIN aspnet_Membership AS D1
ON D1.UserId = D2.UserId
WHERE D4.LastActivity < '2010-01-01 12:00:00.000'

Do I run the DELETE D4, then D2, then D1?

If I do that, the D4 works fine, but D2 and D1 still leave the rows there for the other two tables, aspnet_Users and aspnet_Membership..

Kristen
Test

22859 Posts

Posted - 2010-07-22 : 14:49:09

A DELETE will delete row(s) from one specific table only. You can join other tables (not being deleted from) for example to constrain the data in the main table being deleted.

"If I do that, the D4 works fine, but D2 and D1 still leave the rows there for the other two tables, aspnet_Users and aspnet_Membership.."

Sounds like you need more tables in the mix. More siblings or great-grand-children.

I'm not surprised to hear that, most database structures are convoluted, especially when you want to delete something at the top of the relationship tree.

You might be better off with


SELECT Username
INTO #MyTempTable
FROM MasterTable
WHERE lastActivity < '20100101'

and then a series of


DELETE D
FROM #MyTempTable AS T
     JOIN MasterTable AS M
         ON M.UserName = T.UserName
     JOIN ChildTable AS D
         ON D.SomeID = M.SomeID

you would start off with relationships that get you down to the lowest level - e.g. grand-child-level, then child-level and finally you will be able to delete from the master/parent table (i.e. when all descendants have been deleted)

Backup first! and use TRANSACTION blocks so you can rollback easily and "have another go" if it goes wrong (which is quicker/easier than having to restore from backup)

noonz
Starting Member

33 Posts

Posted - 2010-07-22 : 15:20:57

Thanks Kristen!

I set up a few test tables and got it to work, just made a FULL SQL backup of my production database and about to start running some queries to get this project moving!

Thank you for your help, Ms.Kristen =)

noonz
Starting Member

33 Posts

Posted - 2010-07-22 : 17:34:35

Well, turns out there are a few more tables that hold FK's, I totally fudged my database and had to perform a rollback.

No easier way to purge out and remove all old users?

What should I be doing about foreign keys?

noonz
Starting Member

33 Posts

Posted - 2010-07-22 : 22:59:46

Well, here is the query I built tonight:

BEGIN TRANSACTION

ROLLBACK

SELECT * FROM cs_Users AS D8

--DELETE D1
--DELETE D2
--DELETE D3
--DELETE D4
--DELETE D5
--DELETE D7
DELETE D8

FROM cs_Users AS U
JOIN cs_Users AS D8
ON D8.MembershipId = U.MembershipId
--LEFT OUTER JOIN aspnet_Users AS D7
--ON D7.UserId = D8.MembershipId

--LEFT OUTER JOIN aspnet_Membership AS D5
--ON D5.UserId = D8.MembershipId

--LEFT OUTER JOIN aspnet_Profile AS D4
--ON D4.UserId = D8.MembershipId

--LEFT OUTER JOIN aspnet_UsersInRoles AS D3
--ON D3.UserId = D8.MembershipId

--LEFT OUTER JOIN cs_UserProfile AS D2
--ON D2.MembershipId = D8.MembershipId

--LEFT OUTER JOIN cs_Moderators AS D1
--ON D1.UserId = D8.MembershipId

WHERE D8.LastActivity >= '2010-01-01 00:00:00.000'

After running 1,2,3,4,5,7,8 (missed the 6 ;) )

I went to select from some of the columns in the various tables above, and the old users are still there!

I thought this would be a solid solution, I guess I am still wrong!

Kristen
Test

22859 Posts

Posted - 2010-07-23 : 02:52:51

WHERE D8.LastActivity < '2010-01-01 00:00:00.000'

??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources