sql query structure

Question

Hi,

I have the task of cleaning up some of our data in SQL Server 2008 for CS2009. In our profiles database there are two tables:

1. Addresses

2. UserObject

   Address:

   address_id, line1, line2 guid, varchar, varchar

   UserObject:

   userid_id, addressguids, acitivtydate guid, varchar, datetime

I need to remove all UserObjects that are older than X (i.e. have had not activity). That's obviously not a problem. My issue comes when I want to remove the addresses that belong to that person. They are stored in one field in a concat fashion. For example if the user had 2 addresses it could look something like:

2;{guid1};{guid2}

The first number being the number of guids, all seperated by semi colons. The Address table doesn't have a field for the user_id to be able to tie it back (very annoying). The only ID in the Address table is the address guid.

My question is how I could sort this out in SQL, (my sql is a bit poor). I would imagine it would be something like:

• select all users where activitydate > X
• pull address guid string apart and get each guid
• delete each guid in address table
• delete user

I guess to delete the addresses could substring or something to remove the inital number and semicolon then replace rest of the semi colons with commas and put that into a when IN clause.

I have no clue about the looping, best ways of doing it and the like.

Thanks

Answer 1

i think do it in stages:

first build yourself a new table for user_address. this should have user_id, and address_guid then write a proc or loop to break out the address guids into this new structure.

query it and check that you got it right. wipe it and start over if you didn't.

when done, use this to do the deletes. should be much simpler.

Answer 2

If you don't want to do it using a new table, as previously answered (nothing wrong with that answer, by the way), then you could write a table-valued function that does some string manipulation and returns all the guids in your 'addressguids' field on a new row, with the userid.

Once you've done that, you could then use CROSS APPLY or OUTER APPLY to join the function as if it were a table as described in this technet article.