CODEWITHSUNDEEP
sqlsql-server
arsarc
arsarc

Reputation: 453

How to set a table's field to autonumber using a query in sql

We have a db that has been using social security numbers as the unique ID, and I was hoping to be able to change it so that it could display a unique ID without relying on the SSN. Some tables aren't completely consistent when I took a look.

The only solution I can think up is that I can connect the tables by ssn, but have a autonumber unique ID to join with the ssn... so I can display the autonumber instead of the ssn.

For easier illustration:

Table 1:
+ ------ + --------- +
| SSN    | FirstName |
+ ------ + --------- +
| 111111 | Jeff      |
| 123456 | Kevin     |
| 654321 | Mark      |
| 555555 | David     |
+ ------ + --------- +

Table 2:
+ ------ + ---------- +
| SSN    | Address    |
+ ------ + ---------- +
| 111111 | California |
| 123456 | Texas      |
| 654321 | Arizona    |
+ ------ + ---------- +

FakeIDTable:
+ --- + -- +
| SSN | ID |
+ --- + -- +

This Query:

SELECT Table1.FirstName, Table2.Address, FakeIDTable.ID
FROM (Table1 INNER JOIN Table2 ON Table1.SSN = Table2.SSN)
LEFT JOIN FakeIDTable ON Table1.SSN = FakeIDTable.SSN;

Gives this Result:

+ --------- + ---------- + -- +
| FirstName | Address    | ID |
+ --------- + ---------- + -- +
| Jeff      | California |    |
| Kevin     | Texas      |    |
| Mark      | Arizona    |    |
+ --------- + ---------- + -- |

Is it possible to populate FakeIDTable.ID with an autonumber using Sql?

Update Query1
Set Query1.ID = (autonumber)

or something similar?

Upvotes: 2

Views: 3025

Answers (1)

SqlZim
SqlZim

Reputation: 38023

You could add a column with the identity() property property to the table where ssn is unique for that table and contains all referenced ssns, or create a table that just has the unique ssns and an id, or number some rows using row_number():

Adding an identity column to an existing table that does not have an identity column:

alter table t
  add Id int not null identity(1,1);

Creating a fake table with an identity column:

create table faketable (
    id  int not null identity(1,1) primary key
  , ssn int not null unique
  );

insert into faketable (ssn)
  select distinct ssn
  from t;

Updating a non-identity Id using row_number() using a common table expression:

;with cte as (
  select *
    , rn = row_number() over (order by ssn)
  from t
)
update cte
  set Id = rn;

Updating a non-identity Id using row_number() in a subquery:

update sub
set Id = rn
from (
  select *
    , rn = row_number() over (order by ssn)
  from t
) as sub

rextester demo: http://rextester.com/TDDA51123

Upvotes: 2

Related Questions