Lorik
asked on
how to update one record when there are duplicates?
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?
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?
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
-- 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
ASKER
MS-SQL doesn't have ROWCOUNT funtion, I wish it did.
It does have a ROWCOUNT...
See:
http://premium.microsoft.com/isapi/devonly/prodinfo/msdnprod/msdnlib.idc?theURL=/msdn/library/sdkdoc/sql/tsqlref/src/tsql12_8mt0.htm
But you need to be careful when using it.
See:
http://premium.microsoft.com/isapi/devonly/prodinfo/msdnprod/msdnlib.idc?theURL=/msdn/library/sdkdoc/sql/tsqlref/src/tsql12_8mt0.htm
But you need to be careful when using it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
If you can't modify the table struct, there are possibly other ways.