Thursday, May 14, 2009

Using SET ROWCOUNT to limit number of deleted records

I was given MS SQL database with no normalization, no primary keys in tables and lot of duplicates.
How can you delete a duplicate rows from the table, if you have 2 rows where no unique key exists, all fields are equal, so there is no way to distinguish one row from another?

In Oracle you always have row_id so 2 between 2 rows you can delete a row with max(row_id)

In MS SQL you can use SET ROWCOUNT to limit the number of deleted records.

Run the following example, and you wil get and idea.

create table t
id int,
name varchar(100)

insert t values(1, 'Number One')
insert t values(2, 'Number Two')
insert t values(3, 'Number Three')
insert t values(1, 'Number One')


SELECT count(*) FROM t /* will return 4 */


DELETE from t where ID = 1 /* will delete only one record from 2 records with ID = 1 */


SELECT count(*) FROM t /* will return 3 */


No comments: