Update zero base column to one base

Problem:

A co-worker requested help with a query today…

Derik, I have an Email table with a list of email addresses per person record. There is an ordinal column that is base zero but I decided to make the index in the app for “None” to equal zero. So, I need to update all of the ordinal references to be base 1 so that the column directly relates to the index in the list. I need the script to be re-runnable and only increment the numbers to base one instead of adding one each time that it is run.

Solution:

You can use ROW_NUMBER() with the OVER clause to list your records per person and then update the ordinal to equal the line number.

Let’s walk through it step-by-step. First we’ll create the necessary tables.

CREATE TABLE Person
(
personId UNIQUEIDENTIFIER NOT NULL
CONSTRAINT DF_Person_personId DEFAULT (NEWID()),
firstName VARCHAR(100) NOT NULL,
lastName VARCHAR(100) NOT NULL,
CONSTRAINT PK_Person_personID PRIMARY KEY (personId)
);

CREATE TABLE Email
(
emailId UNIQUEIDENTIFIER NOT NULL
CONSTRAINT DF_Email_emailId DEFAULT (NEWID()),
emailAddress VARCHAR(255) NOT NULL,
ordinalPreference TINYINT NOT NULL,
personId UNIQUEIDENTIFIER NOT NULL
CONSTRAINT FK_Email_personId FOREIGN KEY
REFERENCES dbo.Person(personId)
);

Next we’ll need to populate some base zero test data.

DECLARE @person1 UNIQUEIDENTIFIER = NEWID();
DECLARE @person2 UNIQUEIDENTIFIER = NEWID();

INSERT INTO Person (personId, firstName, lastName)
VALUES (@person1, 'Joe', 'Parker');
INSERT INTO Person (personId, firstName, lastName)
VALUES (@person2, 'Jim', 'Smith');

INSERT INTO Email (emailAddress, ordinalPreference, personId)
VALUES ('fake@address.com', 0, @person1);
INSERT INTO Email (emailAddress, ordinalPreference, personId)
VALUES ('joe@address.com', 1, @person1);
INSERT INTO Email (emailAddress, ordinalPreference, personId)
VALUES ('defre@inbox.com', 2, @person1);

INSERT INTO Email (emailAddress, ordinalPreference, personId)
VALUES ('Jim@Smith.com', 0, @person2);
INSERT INTO Email (emailAddress, ordinalPreference, personId)
VALUES ('Jim.Smith@address.com', 1, @person2);
INSERT INTO Email (emailAddress, ordinalPreference, personId)
VALUES ('punkmaster@DJ.com', 2, @person2);
INSERT INTO Email (emailAddress, ordinalPreference, personId)
VALUES ('jumping@jellybeans.com', 3, @person2);

SELECT E.emailId, P.personId, E.ordinalPreference
FROM Person P
INNER JOIN Email E ON E.personId = P.personId

Update-base-zero-column-1

As you can see we have 3 email addresses for the first person and 4 for the second, each one’s oridinalPreference beginning with zero.

Finally we get to the query that makes the magic.

;WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY personId
ORDER BY ordinalPreference) as [rownum],
personId,
ordinalPreference
FROM Email
)
UPDATE CTE
SET ordinalPreference = rownum
WHERE ordinalPreference <> rownum;

SELECT E.emailId, P.personId, E.ordinalPreference
FROM Person P
INNER JOIN Email E ON E.personId = P.personId

Update-base-zero-column-2

 

By using the row number directly we have satisfied the requirement of this script being safely re-runnable. We aren’t incrementing the value because we don’t want it to happen multiple times. This version will update only the necessary email records each time but also avoid redundant writes with our WHERE clause.

Leave a Reply

%d bloggers like this: