Update statement returns error due to duplicates

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)

Update statement returns error due to duplicates

Author

Topic

hansont
Starting Member

8 Posts

Posted - 2008-02-26 : 22:15:57

Hi All,
When I use the following I get an error. I think it is because of duplicate records in my table.

Update person
Set username = (Select username
From update_person
Where person.firsname = update_person.firstname
and person.lastname = update_person.lastname)

There are a few users that have the same first and last name. How can I ignore the duplicate records and continue to update the table?

Thanks in advance.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-26 : 22:24:44

Do you have complete error message?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 23:07:28

That depends on your requirement. Restrict the records returned from subquery to a single record using TOP 1 or some aggregate functions like MIN(),MAX(),..

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2008-02-27 : 00:29:55

quote:
Originally posted by visakh16

That depends on your requirement. Restrict the records returned from subquery to a single record using TOP 1 or some aggregate functions like MIN(),MAX(),..

visakh, i don't think this is a good idea of using TOP 1 or Aggrigare fuctions into Subquery. Because Update statement will update false values sometime. Instead, let him find out why the duplicate records are being returned by the Subquery. He might have missed some join(s).

Thanks,

Mahesh

hansont
Starting Member

8 Posts

Posted - 2008-02-27 : 10:53:22

Here is the complete error I receive when I run this script.

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Tammy

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-02-27 : 10:58:22

quote:
Originally posted by hansont
There are a few users that have the same first and last name. How can I ignore the duplicate records and continue to update the table?

You'll need a unique natural key common to both tables. Obviously, FirstName/LastName will not suffice.

With a little SQL coding you can restrict your subquery to only those update_person records where the FirstName/LastName combination is unique:

update	person
set	username = usernames.username
from	person
	inner join --usernames  <<avoid correlated subqueries!>>
		(Select	firstname,
			lastname,
			max(username) as username
		From	update_person
		group by firstname,
			lastname
		having count(*) = 1) as usernames
		on person.firstname = usernames.firstname
		and person.lastname = usernames.lastname

This will update only the person records where you can be confident of associating the correct username. (Though it also assumes that FirstName/UserName is unique in your person table).

e4 d5 xd5 Nf6

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-27 : 11:04:27

Rather than join your subquery on the persons first and last name you should join it on your id field:

UPDATE  table1
SET     col1 = ( SELECT t2.col1 + t2.col2
                 FROM   table2 t2
                 WHERE  t2.id = t1.id
               )
FROM    table1 t1

hansont
Starting Member

8 Posts

Posted - 2008-02-27 : 14:42:13

Thanks blindman

Worked perfectly.

hansont
Starting Member

8 Posts

Posted - 2008-02-27 : 21:42:41

Well, I spoke to soon. It is almost perfect.
After running a few queries I realized that there were over a thousand records not updated with the username. There are less than 20 duplicate records. I am not sure what about the code prevented the update on some records. But at least I am closer that I was last night. Any ideas on how to get the other thousand updated?

hansont
Starting Member

8 Posts

Posted - 2008-02-27 : 21:47:55

For further clarification the update_person table is a table that was added to the database. The information from the table was populated with records that I pulled from Active Directory. So there is no common ID between the tables.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-02-28 : 09:13:47

quote:
Originally posted by jdaman

Rather than join your subquery on the persons first and last name you should join it on your id field...

Making assumptions...

quote:
Originally posted by hansont

Well, I spoke to soon. It is almost perfect.
After running a few queries I realized that there were over a thousand records not updated with the username....Any ideas on how to get the other thousand updated?

Yeah, I have a suspicion. I'm thinking that FirstName/LastName/UserName is not unique in your update_person table either. Try this variation:

update	person
set	username = usernames.username
from	person
	inner join --usernames  <<avoid correlated subqueries!>>
		(Select	firstname,
			lastname,
			max(username) as username
		From	(select distinct firstname, lastname, username from update_person) as update_person
		group by firstname,
			lastname
		having count(*) = 1) as usernames
		on person.firstname = usernames.firstname
		and person.lastname = usernames.lastname

e4 d5 xd5 Nf6

hansont
Starting Member

8 Posts

Posted - 2008-02-28 : 19:35:19

After looking at the data further as always with multiple databases, the firstname/lastname in one database maybe entered as Middlename/lastname in the other and Sherrie maybe spelled Sherry etc. Some names are in one database that are not in the other and vice versa. I'm not sure what to do at this point. Firstname/Lastname was the only common thread between the two. I am very limited in my sql skills so any ideas would be great.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-02-29 : 10:24:39

This function will compare two text strings and generate a number indicating their degree of similarity. You can run it against the concatenated name values in your two database tables to return the most likely matches:
http://sqlblindman.googlepages.com/fuzzysearchalgorithm
I would create a column in your update_person table to hold the primary key from your person table (add one if you do not have one), and then run progressively less restrictive comparisons between the two tables to update the person_update records with the matching person ID values.

e4 d5 xd5 Nf6

hansont
Starting Member

8 Posts

Posted - 2008-02-29 : 20:04:12

I'll give it a try.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources