SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need to update table to unique values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

limester
Starting Member

11 Posts

Posted - 11/27/2013 :  09:55:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/27/2013 :  10:06:29  Show Profile  Reply with Quote
1. devise a query that will select only the values you want to keep
2. 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 Optimizer
TG
Go to Top of Page

limester
Starting Member

11 Posts

Posted - 11/27/2013 :  12:42:20  Show Profile  Reply with Quote
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 Index
1234 1234
1234 1234
1234 1234
4321 4321
4321 4321
4321 4321

Instead, since the update I see example B:

ID Index
1234 1234
1234 1222
1234 1562
4321 4321
4321 1456
4321 3278

So I need to somehow update the index now so it reflects the good example A.

I hope that helps. Thanks in advance!
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/27/2013 :  14:57:08  Show Profile  Reply with Quote
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 Index
1234 1234
1234 1234
1234 1234
4321 4321
4321 4321
4321 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 link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

Edited by - TG on 11/27/2013 14:59:20
Go to Top of Page

limester
Starting Member

11 Posts

Posted - 11/27/2013 :  15:18:05  Show Profile  Reply with Quote
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!

Edited by - limester on 11/27/2013 15:19:33
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/27/2013 :  16:41:05  Show Profile  Reply with Quote
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 Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000