(muzamil@hotmail.com) writes:
> To get rid of redundant data in a table, my cleint will be providing
> something like this:
>
> IDtokeep Ids to delete
> 34 24,35,49
> 12 14,178,1457
> 54 32,65,68

Undoubtedly it would be a whole lot easier if your client could just
give you plain tuples:

34 24
34 35
34 49
12 14
12 178

Then it's all a plain update statement and a plain delete.

With the current scheme, you need to run a string-to-table function,
and you need to loop row by row. (In SQL 2000. In SQL 2005 you can
do it in one statement, but you still need the string-to-table
function.)

> I have to write a script for each of the above rows which looks like
> this:
> -----------------------------------
> update sometable
> set id = 34
> where id in (24,35,49)
>
> delete from sometable
> where id in (24,35,49)
> -----------------------------------
> As I said I have to do this for EACH row. Can I somehow automate this
> or will I need to write to same script for each row (there are about
> 5000 rows in this audit table)

Well, you can actually do it without the string-to-table function,
with some manual intervention:

SELECT 'UPDATE somtable SET id = ' + ltrim(str(IdTokeep)) +
' where id in (' +
IdsTodelete + ')
DELETE sometable where id in (' + IdsTodelete + ')'
FROM dataclean

And then cut and paste result.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp