Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Sql Server give ID's

Posted on 2011-09-27
11
182 Views
Last Modified: 2012-05-12
I have the first two columns in a table.  i want to create a third column just like I have to give it an ID.  Notice that depending on if the value of the second column repeats itself for the same value in column one, i want to increment by 1, like i have shown you in the grid here. So essentially i want to create the 3rd column (ID) in code.

Just can't figure out how to do this in code.  Can someone help.

Column 1      Column 2      ID
46      23      1
46      26      1
46      27      1
46      23      2
47      27      1
47      24      1
49      24      1
50      24      1
50      24      2
50      27      1
0
Comment
Question by:joygomez
  • 7
  • 4
11 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 36713999
this query will give you third column

select col1, col2, row_number() over (partition by col1, col2 order by ???) rn
from myTable

then join this with original and update your col3 (you may not need this step since you can always get it with query above)


0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36714029
to update it

update t
set t.col3=x.rn
from test3 t inner join
(select col1, col2, sort, row_number() over (partition by col1, col2 order by [sort]) rn from test3) x
on t.col1=x.col1 and t.col2=x.col2 and t.[sort]=x.[sort]
0
 

Author Comment

by:joygomez
ID: 36714038
HainKurt,
Can you be a bit more specific with the syntax.  I am quite new to sql...:(  what is the ??? and rn for.
Thanks a lot
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:joygomez
ID: 36714065
ok, i am getting errors... can you make this dummy proof for me please.  

this is my table - Intuit_Staging_Invoice
this is the first column in the above table - IntuitQboInvoiceId
this is the second column in the above table - IntuitQboItemId
the third column is what needs to be updated  - InvoiceItemInternalId
0
 

Author Comment

by:joygomez
ID: 36714086
what is test3
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36714148
here it is

update t
set t.InvoiceItemInternalId=x.rn
from Intuit_Staging_Invoice t inner join
(select IntuitQboInvoiceId, IntuitQboItemId, sort, row_number() over (partition by IntuitQboInvoiceId, IntuitQboItemId order by [sort]) rn from Intuit_Staging_Invoice) x
on t.IntuitQboInvoiceId=x.IntuitQboInvoiceId and t.IntuitQboItemId=x.IntuitQboItemId and t.[sort]=x.[sort]

we need another column in your table properly sort and find the record, I used [sort] column... are these the only 3 columns in your table? no other column? like create date or some other column?
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 36714171
if you dont have any other column, we may need additional step to properly find & sort the record....

Step1:

create a new table: tmp(id - identity column, col1,col2)

then

do these






set identity_insert Intuit_Staging_Invoice on;

insert into tmp(col1,col2) select * from Intuit_Staging_Invoice;

delete from Intuit_Staging_Invoice;

set identity_insert Intuit_Staging_Invoice off;

insert into Intuit_Staging_Invoice(IntuitQboInvoiceId, IntuitQboItemId, InvoiceItemInternalId)
select IntuitQboInvoiceId, IntuitQboItemId, row_number() over (partition by IntuitQboInvoiceId, IntuitQboItemId order by id) from tmp;

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36714174
oops, sorry, identity insert should be turned on tmp
set identity_insert tmp on;

insert into tmp(col1,col2) select * from Intuit_Staging_Invoice;

set identity_insert tmp off;

delete from Intuit_Staging_Invoice;

insert into Intuit_Staging_Invoice(IntuitQboInvoiceId, IntuitQboItemId, InvoiceItemInternalId)
select IntuitQboInvoiceId, IntuitQboItemId, row_number() over (partition by IntuitQboInvoiceId, IntuitQboItemId order by id) from tmp;

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36714180
we can do it without and identity column on tmp

create a new table: tmp (IntuitQboInvoiceId, IntuitQboItemId, InvoiceItemInternalId)


insert into tmp select *, row_number() over (order by 1) from Intuit_Staging_Invoice;

delete from Intuit_Staging_Invoice;

insert into Intuit_Staging_Invoice(IntuitQboInvoiceId, IntuitQboItemId, InvoiceItemInternalId)
select IntuitQboInvoiceId, IntuitQboItemId, row_number() over (partition by IntuitQboInvoiceId, IntuitQboItemId order by InvoiceItemInternalId) from tmp;

Open in new window

0
 

Author Closing Comment

by:joygomez
ID: 36714184
Thanks,
got it to work....
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36714188
or we can use select into to create table automatically & insert in one shot



select * into tmp
from (
select IntuitQboInvoiceId, IntuitQboItemId, row_number() over (partition by IntuitQboInvoiceId, IntuitQboItemId order by 1) from Intuit_Staging_Invoice
) x;

delete from Intuit_Staging_Invoice;

insert into Intuit_Staging_Invoice(IntuitQboInvoiceId, IntuitQboItemId, InvoiceItemInternalId)
select IntuitQboInvoiceId, IntuitQboItemId, row_number() over (partition by IntuitQboInvoiceId, IntuitQboItemId order by InvoiceItemInternalId) from tmp;

Open in new window

0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question