{"id":68,"date":"2014-05-28T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/update-zero-base-column-to-one-base"},"modified":"2026-03-18T21:50:59","modified_gmt":"2026-03-18T21:50:59","slug":"update-zero-base-column-to-one-base","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2014\/05\/28\/update-zero-base-column-to-one-base\/","title":{"rendered":"Update zero base column to one base"},"content":{"rendered":"<h2>Problem:<\/h2>\n<p>A co-worker\u00a0requested help with a query today\u2026<\/p>\n<p>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\u00a0in the app for \u201cNone\u201d 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.<\/p>\n<h2>Solution:<\/h2>\n<p>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.<\/p>\n<p>Let\u2019s walk through it step-by-step. First we\u2019ll create the necessary tables.<\/p>\n<pre><code class=\"language-\">CREATE TABLE Person\n(\npersonId UNIQUEIDENTIFIER NOT NULL \n    CONSTRAINT DF_Person_personId DEFAULT (NEWID()),\nfirstName VARCHAR(100) NOT NULL,\nlastName VARCHAR(100) NOT NULL,\nCONSTRAINT PK_Person_personID PRIMARY KEY (personId)\n);\n\n<p>CREATE TABLE Email\n(\nemailId UNIQUEIDENTIFIER NOT NULL \n    CONSTRAINT DF_Email_emailId DEFAULT (NEWID()),\nemailAddress VARCHAR(255) NOT NULL,\nordinalPreference TINYINT NOT NULL,\npersonId UNIQUEIDENTIFIER NOT NULL \n    CONSTRAINT FK_Email_personId FOREIGN KEY \n    REFERENCES dbo.Person(personId)\n);<\/code><\/pre>\n<\/p>\n<p>Next we\u2019ll need to populate some base zero test data.<\/p>\n<pre><code class=\"language-\">DECLARE @person1 UNIQUEIDENTIFIER = NEWID();\nDECLARE @person2 UNIQUEIDENTIFIER = NEWID();\n\n<p>INSERT INTO Person (personId, firstName, lastName)\nVALUES (@person1, 'Joe', 'Parker');\nINSERT INTO Person (personId, firstName, lastName)\nVALUES (@person2, 'Jim', 'Smith');<\/p>\n\n<p>INSERT INTO Email (emailAddress, ordinalPreference, personId)\nVALUES ('fake@address.com', 0, @person1);\nINSERT INTO Email (emailAddress, ordinalPreference, personId)\nVALUES ('joe@address.com', 1, @person1);\nINSERT INTO Email (emailAddress, ordinalPreference, personId)\nVALUES ('defre@inbox.com', 2, @person1);<\/p>\n\n<p>INSERT INTO Email (emailAddress, ordinalPreference, personId)\nVALUES ('Jim@Smith.com', 0, @person2);\nINSERT INTO Email (emailAddress, ordinalPreference, personId)\nVALUES ('Jim.Smith@address.com', 1, @person2);\nINSERT INTO Email (emailAddress, ordinalPreference, personId)\nVALUES ('punkmaster@DJ.com', 2, @person2);\nINSERT INTO Email (emailAddress, ordinalPreference, personId)\nVALUES ('jumping@jellybeans.com', 3, @person2);<\/p>\n\n<p>SELECT E.emailId, P.personId, E.ordinalPreference\nFROM Person P\nINNER JOIN Email E ON E.personId = P.personId<\/code><\/pre>\n<\/p>\n<p><a href=\"\/wp-content\/uploads\/Update-base-zero-column-1.png\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/Update-base-zero-column-1.png\" alt=\"\" \/><\/a><\/p>\n<p>As you can see we have 3 email addresses for the first person and 4 for the second, each one\u2019s oridinalPreference beginning with zero.<\/p>\n<p>Finally we get to the query that makes the magic.<\/p>\n<pre><code class=\"language-\">;WITH CTE AS \n (\n SELECT ROW_NUMBER() OVER (PARTITION BY personId \n     ORDER BY ordinalPreference) as [rownum],\n     personId,\n     ordinalPreference\n FROM Email\n )\nUPDATE CTE\nSET ordinalPreference = rownum\nWHERE ordinalPreference &lt;&gt; rownum;\n\n<p>SELECT E.emailId, P.personId, E.ordinalPreference\nFROM Person P\nINNER JOIN Email E ON E.personId = P.personId<\/code><\/pre>\n<\/p>\n<p><a href=\"http:\/\/www.sqlhammer.com\/blog\/wp-content\/uploads\/2014\/05\/Update-base-zero-column-2.png\"><img decoding=\"async\" src=\"http:\/\/www.sqlhammer.com\/blog\/wp-content\/uploads\/2014\/05\/Update-base-zero-column-2.png\" alt=\"\" \/><\/a><\/p>\n<p>By using the row number directly we have satisfied the requirement of this script being safely re-runnable. We aren\u2019t incrementing the value because we don\u2019t 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.<\/p>\n<hr \/>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/dh.jpg\" alt=\"\" \/><strong>Written by Derik Hammer of <a href=\"http:\/\/www.sqlhammer.com\/\">SQL Hammer<\/a><\/strong><\/p>\n<p>Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around <a href=\"http:\/\/www.sqlhammer.com\/blog\/category\/high-availability\/\">high-availability<\/a>, <a href=\"http:\/\/www.sqlhammer.com\/blog\/category\/general\/disaster-recovery\/\">disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n<p>Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started <a href=\"http:\/\/www.sqlhammer.com\/\">SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n<p>Derik is the owner and lead author of SQL Hammer, a <a href=\"http:\/\/www.sqlhammer.com\/\">Microsoft SQL Server resource<\/a>.<\/p>\n<p>For more information, visit <a href=\"http:\/\/www.sqlhammer.com\/\">http:\/\/www.sqlhammer.com<\/a>. Follow Derik on <a href=\"http:\/\/twitter.com\/SQLHammer\">Twitter<\/a> for <strong>SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Problem: A co-worker\u00a0requested help with a query today\u2026 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\u00a0in the app for \u201cNone\u201d to equal zero. So, I need to update all of the ordinal [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":527,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-68","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/68","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=68"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/68\/revisions"}],"predecessor-version":[{"id":331,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/68\/revisions\/331"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/527"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=68"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=68"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=68"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}