correct invalid records - 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)

correct invalid records

Author

Topic

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-09-29 : 16:19:43

I have a table where there are errors in the data. A person can have multiple records in the table, but only one of the records should have a primaryrole value of 'T'. The following script creates a table and selects the personid(s) who have an error in their records. My question is what is the simplest way to correct the values (e.g. using a stored procedure). The logic of the correction would be that the top record with the highest role value is set to 'T' and all the others for that person are set to 'F'. If there is more than 1 record with the same rolevalue any of them can be chosen.
thanks for your thoughts,

create table Sample2 (PersonID varchar(2), PrimaryRole varchar(1), RoleValue integer)
Insert into Sample2 (PersonID, PrimaryRole, RoleValue)
Select '01', 'T', 1
Insert into Sample2 (PersonID, PrimaryRole, RoleValue)
Select '01', 'T', 2
Insert into Sample2 (PersonID, PrimaryRole, RoleValue)
Select '01', 'T', 3
Insert into Sample2 (PersonID, PrimaryRole, RoleValue)
Select '01', 'T', 3
Insert into Sample2 (PersonID, PrimaryRole, RoleValue)
Select '01', 'F', 1
Insert into Sample2 (PersonID, PrimaryRole, RoleValue)
Select '01', 'F', 1

Insert into Sample2 (PersonID, PrimaryRole, RoleValue)
Select '02', 'T', 1
Insert into Sample2 (PersonID, PrimaryRole, RoleValue)
Select '02', 'F', 2
Insert into Sample2 (PersonID, PrimaryRole, RoleValue)
Select '02', 'F', 3
Insert into Sample2 (PersonID, PrimaryRole, RoleValue)
Select '02', 'F', 1

select personid, primaryrole, count(personid) as CountErrorRecords from Sample2
group by personID, primaryrole having primaryrole = 'T' and count(personid) <> 1

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-29 : 16:33:15

Is there a primary key on the table? Or any column(s) that will guarantee to make a row unique? If not and there are multiple rows for a given personid with the same (highest) RoleValue then there is no way to update just one of those rows and not the other - without adding some unique-ifier to the table.

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-29 : 17:26:59

This will get the rows you want to set to "T"

You can just set all the others to "F" where they are not equal


SET NOCOUNT ON
GO

CREATE TABLE #Sample2 (
	  PersonID varchar(2)
	, PrimaryRole varchar(1)
	, RoleValue integer
)
GO

INSERT INTO #Sample2 (PersonID, PrimaryRole, RoleValue)
SELECT '01', 'T', 1 UNION ALL
SELECT '01', 'T', 2 UNION ALL
SELECT '01', 'T', 3 UNION ALL
SELECT '01', 'T', 3 UNION ALL
SELECT '01', 'F', 1 UNION ALL
SELECT '01', 'F', 1 UNION ALL
SELECT '02', 'T', 1 UNION ALL
SELECT '02', 'F', 2 UNION ALL
SELECT '02', 'F', 3 UNION ALL
SELECT '02', 'F', 1
GO

SELECT PersonID, MAX(RoleValue), MAX(ROW) 
  FROM (
		SELECT *, ROW_NUMBER() OVER (ORDER BY PersonID, RoleValue DESC) AS ROW
		  FROM #Sample2
) AS XXX
GROUP BY PersonID
GO

DROP TABLE #Sample2
GO

SET NOCOUNT OFF
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-09-30 : 03:04:41

yes there is a primary key - RoleID (sorry)

create table Sample2 (RoleID uniqueidentifier, PersonID varchar(2), PrimaryRole varchar(1), RoleValue integer)
Insert into Sample2 (RoleID, PersonID, PrimaryRole, RoleValue)
Select newid(), '01', 'T', 1
Insert into Sample2 (RoleID, PersonID, PrimaryRole, RoleValue)
Select newid(), '01', 'T', 2
Insert into Sample2 (RoleID, PersonID, PrimaryRole, RoleValue)
Select newid(), '01', 'T', 3
Insert into Sample2 (RoleID, PersonID, PrimaryRole, RoleValue)
Select newid(), '01', 'T', 3
Insert into Sample2 (RoleID, PersonID, PrimaryRole, RoleValue)
Select newid(), '01', 'F', 1
Insert into Sample2 (RoleID, PersonID, PrimaryRole, RoleValue)
Select newid(), '01', 'F', 1

Insert into Sample2 (RoleID, PersonID, PrimaryRole, RoleValue)
Select newid(), '02', 'T', 1
Insert into Sample2 (RoleID, PersonID, PrimaryRole, RoleValue)
Select newid(), '02', 'F', 2
Insert into Sample2 (RoleID, PersonID, PrimaryRole, RoleValue)
Select newid(), '02', 'F', 3
Insert into Sample2 (RoleID, PersonID, PrimaryRole, RoleValue)
Select newid(), '02', 'F', 1

select personid, primaryrole, count(personid) as CountErrorRecords from Sample2
group by personID, primaryrole having primaryrole = 'T' and count(personid) <> 1

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-09-30 : 03:53:59

this finds the row - but not the roleid of the row - and in fact it is the other rows for this person that I need to set to 'F' and retain this one as 'T'

SELECT PersonID, PrimaryRole, MAX(RoleValue) as RoleValue, MAX(ROW) as RowNumber
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY PersonID, RoleValue DESC) AS ROW
FROM Sample2
) AS XXX
GROUP BY PersonID, primaryrole having primaryrole = 'T' and count(personid) <> 1

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-30 : 09:48:28

Ok - then this should do it:


select s.RoleID
       ,s.personid
       ,case when t.personid is null then 'F' else 'T' end as PrimaryRole
       ,s.roleValue

--update s set primaryRole = case when t.personid is null then 'F' else 'T' end

from   sample2 s
left outer join
       (
       select personid
              ,ca.RoleID
       from sample2 s
       cross apply (
              select top 1 RoleID
              from sample2 
              where personid = s.personid 
              order by RoleValue desc
              ) ca
       group by personid
              ,ca.RoleID
       ) T
              on t.roleid = s.roleid

Be One with the Optimizer
TG

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-09-30 : 15:19:15

TG thank you - I am away in the Netherlands until Monday - looks good though and I will try solution when I am back

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-10-06 : 08:59:41

Great - thank you TG - works perfectly
To prove the old and new values I added s.primaryrole and t.personid to the initial selection
select s.RoleID
,s.personid, s.PrimaryRole
,case when t.personid is null then 'F' else 'T' end as PrimaryRoleNewValue
,s.roleValue, t.personid ......
again thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources