Generate serial numbers and update column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Generate serial numbers and update column

Author

Topic

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2013-07-17 : 14:25:28

I have two tables. One table has empty column (ID) and I want to generate serial numbers in that column based two tables columns(LoanNum) condition.


Table A

ID      LoanNum
Null    1234
Null    2345
Null    3456
Null    4567
Null    5678
Null    6789

Table B

LoanNum
1234
2345
3456
4567
5678
6789
2324
4352
4235

Id is not primary key, but should not duplicates keys, it is just a column want to generate serial number (1,2,3,4...etc) How to generate?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-07-17 : 15:37:37

I'm not 100% sure I understand what you are after but you can just turn it into an IDENTITY column if you just want a unique number.
Alternatively in 2012 you can use a SEQUENCE and populate each row with .NEXTVAL

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2013-07-17 : 17:24:52

quote:
Originally posted by LoztInSpace

I'm not 100% sure I understand what you are after but you can just turn it into an IDENTITY column if you just want a unique number.
Alternatively in 2012 you can use a SEQUENCE and populate each row with .NEXTVAL

The thing is ID column newly added. So I have to update that column matching with Table B LoanNum column with Table A LoanNum column. If LoanNum doesn't match with TableA LoanNum then ID column should be null.

Table A

ID      LoanNum
1       1234
2       2345
3       3456
4       4567
7       5678
8       6789
Null    5534
Null    4345

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-07-17 : 18:23:16

Strange requirement. What are you using it for?
What if LoanNum appears twice in either table?

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2013-07-17 : 19:30:00

quote:
Originally posted by LoztInSpace

Strange requirement. What are you using it for?
What if LoanNum appears twice in either table?

Insert the same number. But it will not same loan number in the table.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-17 : 20:31:18

you can do it in two steps as shown below:
[CODE]
-- TEST DATA

DECLARE @temp1 TABLE (ID INT, LoanNum INT);
INSERT INTo @temp1 VALUES
(NULL, 1234),
(Null, 2345),
(Null, 3456),
(Null, 4567),
(Null, 5678),
(Null, 6789),
(Null, 5534),
(Null, 4345);

DECLARE @temp2 TABLE(LoanNum INT);
INSERT INTO @temp2 VALUES
(1234),
(2345),
(3456),
(4567),
(5678),
(6789),
(2324),
(4352),
(4235);

-- UPDATE QUERIES

UPDATE @temp1
SET ID = T2.RN FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY LoanNum) as RN, *
FROM @temp1) T2 INNER JOIN @temp1 T1 ON T1.LoanNum = T2.LoanNum;

UPDATE T1
SET ID = NULL FROM @temp1 T1 WHERE T1.LoanNum NOT IN (SELECT LoanNum FROM @temp2);

[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources