update duplicate records with default value

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 2008 Forums

Transact-SQL (2008)

update duplicate records with default value

Author

Topic

varalakshmi
Yak Posting Veteran

98 Posts

Posted - 2011-11-07 : 03:28:34

Hi,
I have a stage table without any key and constraints.
We have a column named 'errorid' in the same table which has to be updated with default value if the record is a duplicate.

Please help me in handling this.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 04:26:28

you want this updation to happen during data population step itself? or does it need to be seperate validation task?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

varalakshmi
Yak Posting Veteran

98 Posts

Posted - 2011-11-07 : 06:09:23

It has to be in a separate data validation step

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 06:11:33

quote:
Originally posted by varalakshmi

It has to be in a separate data validation step

ok. and whats the default value you need to put for errorid?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

varalakshmi
Yak Posting Veteran

98 Posts

Posted - 2011-11-07 : 06:52:45

The default value should be 0.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 06:55:36

ok. what according to you represents a duplicate. show some sample data and explain how duplicates are identified

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

varalakshmi
Yak Posting Veteran

98 Posts

Posted - 2011-11-07 : 07:32:30

For example , the table has six columns, col1,col2,col3,col4,col5 and errorid.
if the combination of all col1, col2, col3,col4 and col5 are matching, only the ducplicate copy of the record should be update.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 09:18:59

ok here you go


UPDATE t
SET t.errorid=0
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2, col3,col4,col5 ORDER BY col1) AS Rn,errorid
FROM tablename
)t
WHERE Rn > 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

varalakshmi
Yak Posting Veteran

98 Posts

Posted - 2011-11-08 : 00:29:21

Thanks a lot for the solution.
Will this affect the performance in case the volume of the data is huge.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 00:37:10

quote:
Originally posted by varalakshmi

Thanks a lot for the solution.
Will this affect the performance in case the volume of the data is huge.

what volume of data you're looking at?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

varalakshmi
Yak Posting Veteran

98 Posts

Posted - 2011-11-09 : 01:07:35

The volume would be 100 million records approximately.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 01:16:31

quote:
Originally posted by varalakshmi

The volume would be 100 million records approximately.

i think it should be ok
in case its not performing upto expectations
you can put the records to temporary table first using select and then add an index on pk field and using join you can update it from main table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources