How to update a table that causes temporary duplicate index

Question

How to update a table that causes temporary duplicate index

roy.tollison

SSCommitted

Points: 1930
More actions
June 9, 2019 at 3:58 pm

#3651575

I have a table with a column that stores a set of sequential numbers and is an index with no duplicates. i need to know how to update those number by adding a select value to it.
example: the column in question has 0,1,2,3,4,5,6,7,8,9,10....
lets say i need to add 5 to that column for every row. so on the first update the 0 becomes 5 and is a duplicate.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246990 More actions · Answer 1

One obvious way is to disable the index & then rebuild it, once the update is complete. I presume you're trying to avoid that?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

roy.tollison SSCommitted Points: 1930 More actions · Answer 2

Yes I would like to avoid removing the index and then adding it back. I have already started setting up my code to do that but then thought there has to be a better way?

DesNorton SSC-Insane Points: 24417 More actions · Answer 3

If I understand the requirements, a simple UPDATE statement should do the trick.

This will increment every record by 5.

UPDATE YourTable
SET UniqueField = UniqueField + 5;

Although not recommended, sometimes you are left with a situation where you have to loop through the data in order to achieve your goal. In this case, you would need to loop backwards (from the highest value to the lowest). This way you create a new high value, and open up a spot for a lower value to update into.

How to post data/code on a forum to get the best help.

David Burrows SSC Guru Points: 65143 More actions · Answer 4

Not sure of your exact requirement but

UPDATE #table SET num = -(num + 1) WHERE num >= 5;
UPDATE #table SET num = 5 WHERE num = 0;
UPDATE #table SET num = -num WHERE num < 0;

Although this is probably less efficient than dropping and recreating the index on a large number of rows.

Far away is close at hand in the images of elsewhere.
Anon.

ScottPletcher SSC Guru Points: 101126 More actions · Answer 5

Note that you don't have to drop the index and re-create it.

Instead you can DISABLE it, then REBUILD it.

ALTER INDEX no_dups_index ON dbo.your_table DISABLE;

UPDATE your_table

SET ... = ... + 5

/*WHERE ... */

ALTER INDEX no_dups_index ON dbo.your_table REBUILD;

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 6

In either case, you need to determine if the index is being used in association with FKs before you either drop or disable the index. IIRC, doing either will cause a related FK to also be disabled. It's a pretty good bet that if the index is not UNIQUE in nature, you won't have that concern.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

roy.tollison SSCommitted Points: 1930 More actions · Answer 7

Sorry for late replies, but all this turns out to be a moot issue. I wrote the script for someone else and they were running it and told me that this table was not getting updated because the column in question was an index that did not allow duplicates. Well long story short is "Trust but verify."

The index was duplicates allowed. So after testing the script again the message was for a broken page on the table.

So again thanks for all the info and sorry for all the trouble. I will always verify in the future. (no matter how long we have been friends and I trusted his information).

John Esraelo-498130 SSCertifiable Points: 5916 More actions · Answer 8

If this column is not acting as a foreign key to another DB object(s) then perhaps my suggestion may raise eye-brows.

Add a new col, put the values that you wish for each row, drop the original col, and rename the new col with the old col name.

crazy but might work, especially if you have a very large table.

Cheers,
John Esraelo

ThatDataGuy SSC Rookie Points: 33 More actions · Answer 9

When you drop the original column, the index associated with that column will be dropped. Then after you rename the new column to its old (original) name, the index will need to be rebuilt. Yes, it will work, but at the end this still involves re-building the index. So why not just disable the index and rebuild it? That to me is the best way to do it.