We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

how to update one record when there are duplicates?

Lorik
Lorik asked
on
Medium Priority
192 Views
Last Modified: 2010-03-19
Is there a way to update only one record when there are duplicate records in the table?  
The reason there are duplicates is because the distinct key fields haven't been populated yet, but I need to perform an update on only one particular record before I can fill in the distinct key fields.  Any suggestions?
Comment
Watch Question

Commented:
An easy way would be to have an identity field in your table.  On the insert, you could return the @@Identity variable to your code from a stored proc that does the insert.  With that value, you would have a unique criteria for the where clause for the update.

If you can't modify the table struct, there are possibly other ways.

Commented:
I know this would work in Sybase SQL Server, not sure in MS SQL, but give it a try (on a test table, please!).
-- limit action to a single row
set rowcount 1
go
update mytable
where dupkeyvalue = <x>
go
-- return to affecting full set of rows
set rowcount 0
go

Author

Commented:
MS-SQL doesn't have ROWCOUNT funtion, I wish it did.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
Lorik,

I'm not sure I understand exactly what you're trying to do.  If I understand your question, you might want to do an UPDATE...WHERE, something like:

UPDATE table SET <whatever>
WHERE <afield> = (select distinct <afield> from table)

This may give you a starting point, again depending on what exactly you want to do.

Don

Commented:
Lorik,

Sorry, I wasn't thinking straight.  What I wrote before will not work.  Please ignore it.

If you want to update only one row, you have to find something unique about it.  It doesn't have to be a key, but it has to be unique.  

Otherwise you have to do something procedural like using a cursor to find the set, fetch the first record and then update it.  Let us know if you need help with this.  :)

Don
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.