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.
| Author |
Topic |
|
limester
Starting Member
11 Posts |
 Posted - 2013-11-27 : 09:55:39
|
| Hello,I am looking for a way to align a unique value in the SQL database that has become out of sync with a recent update statement.This column value currently has up to 3 values per ID. I need to update the DB so it only has 1 value per ID, like it did before the update statement was run. I can locate the good/bad values using a SQL query, but I am not sure how I can update and align the 40,000+ records that are now out of sync.I hope that helps to explain the issue, please let me know.Thanks in advance! |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-11-27 : 10:06:29
|
| 1. devise a query that will select only the values you want to keep2. delete all rows that are not in the results of step 1.3. Add primary keys, unique constraints, foreign key constraints to all your tables to help ensure the integrity of your data.if you need help with steps 1 or 2:first try searching this site for example of "removing duplicates". If you can't get answers from that then you'll need to post your table structure and define what logically should determine uniqueness and how to decide which dupe to keep and which others can be deleted.Be One with the OptimizerTG |
 |
|
|
limester
Starting Member
11 Posts |
Posted - 2013-11-27 : 12:42:20
|
| Hi,Thanks for the reply!I do not think that I explained this properly, so I think it is better if I give examples.I have an ID column and I have an index column. Typically I would like to see example A:ID Index1234 12341234 12341234 12344321 43214321 43214321 4321Instead, since the update I see example B:ID Index1234 12341234 12221234 15624321 43214321 14564321 3278 So I need to somehow update the index now so it reflects the good example A.I hope that helps. Thanks in advance! |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-11-27 : 14:57:08
|
I suspect you still didn't explain this properly  Your title mentions "update to unique values". But your "like to see sample A" contains duplicate rows ???quote:
ID Index1234 12341234 12341234 12344321 43214321 43214321 4321
if [Index] is supposed to be the same value as [ID] then just:update yourTable set [Index] = [ID] where [Index] != [ID]EDIT:Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxBe One with the OptimizerTG |
|
|
|
limester
Starting Member
11 Posts |
Posted - 2013-11-27 : 15:18:05
|
| Hi,Thanks for the response, yes you are correct the topic is very misleading.They are not duplicate rows though, I have just extracted the 2 main columns I need to work with, as the row contains multiple other columns of unique data.I think what you recommend will work.I will try this.Thanks! |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-11-27 : 16:41:05
|
| Then I would wonder why you have two columns that should always have the same values.In general when performing updates I usually take any or all of several safeguards:- back up the table to a new table- run the update first as a SELECT just to see what I am about to update.- run the update in a transaction and check the results before running the COMMIT.Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Need to update table to unique values