Last post Apr 14, 2016 07:38 PM by PatriceSc
Apr 14, 2016 06:25 PM|Ken Carter|LINK
Hours of work manually editing a column to provide a numeric index column for my table and found that I had messed up and left a few out and needed to insert them back in. Rather than go though another set of hours and hours of editing and possibly making
more manual errors I created another column and copied over the content of my index column and appended a zero to the end of my index this way where I had nulls I could increment 1.2.3..etc between the gaps to fix my index the new column and the rewrite my
index column and remove my temporary maintenance column. Sounded like a good plan at the time... till I got up to the part where I was writing the data back to the column I was trying to repair.
I got the brilliant idea that I could use something like this:
SET EEIndex = ROW_NUMBER() OVER (ORDER BY EEIndex)
WHERE Article > ''
But found out that ROW_NUMBER() works really well in a select... but not in an update..
So here is the question.. I have a simple table. with my two index columns EIndex and EEIndex The temporary one (and correct order) is EEIndex. The one that needs to reflect the row numbers in the order of EEIndex is EIndex. Or to state this in another
way. If listed by order of EEIndex and incremented from 1 to the end record EIndex should number 1 though 1305 (the total number of records in the table).
I'm banging my head against the wall trying to work out a way to update this without having to manually edit all 1305 records again. HELP...PLEASE!
Apr 14, 2016 07:20 PM|Ken Carter|LINK
I found a solution that worked perfectly!! Here it is for anyone that wants to do the same thing some time...
;WITH CTE AS
ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS RunningNumber
SET EIndex = RunningNumber
SELECT * FROM GETT_Elements
It worked like a champ!
Thanks for listening
Apr 14, 2016 07:38 PM|PatriceSc|LINK
You can rewrite the update to compute the row number in a separate select query ie somethng such as :
JOIN (SELECT oldIndex,ROW_NUMBER() OVER (ORDER BY OldIndex) AS rowNumber FROM YourTable) s ON s.oldIndex=YourTable.OldIndex
Adapt as you want. I started but I'm afraid you are confusing EIndex and EEIndex and that my solution would update the wrong column (my understanding would be that you want to udpate EIndex rather than EEIndex?)
The source problem is a bit unclear.Gaps shouldn't be a problem (you'll get them back as soon as you'll delete a row). Another option could be to add an identity column ie :
CREATE TABLE t(Code VARCHAR(10) CONSTRAINT old_pk PRIMARY KEY)
INSERT INTO t(Code) VALUES('A'),('B'),('C'),('D')
SELECT * FROM t
ALTER TABLE t ADD pk INT IDENTITY NOT NULL
ALTER TABLE t DROP CONSTRAINT old_pk
ALTER TABLE t ADD CONSTRAINT new_pk PRIMARY KEY(pk)
SELECT * FROM t
--DROP TABLE t