April 28, 2005 at 10:39 am
I am seeking some assistance on why my Update statement does not produce the results I would like to see in the idientifiaction_id field as represented below. I know I have seen others post this problem to this site before and I have even read how to do it in SQLServerStandard. I can't seem to find the article or copy of the sql code that I probably saved somewhere. Anyway, I am not updating the indentifcation_id (int) column at all. It remains NULL after the Update runs. Runs, seems to work, nothing updated.
UPDATE person_identif
SET identification_id = identification_id + 1
SELECT TOP 100 PERCENT dbo.person_identif.person_id, dbo.person_identif.identification_id
FROM dbo.person_identif INNER JOIN
dbo.person_identif person_identif_1 ON dbo.person_identif.person_id = person_identif_1.person_id
ORDER BY dbo.person_identif.person_id
Person_id identification_id
1 1
2 1
3 1
4 1
5 1
5 2
5 3
6 1
6 2
6 3
6 4
Eternally Grateful DBA
April 28, 2005 at 11:26 am
What you have here is 2 separate, unrelated, statements.
Can you post the table definition along with some sample data. We'll also need to see what results you are trying to generate.
April 28, 2005 at 2:29 pm
Thanks Remi for taking a shot at this. My table def and sample data is below. The update I want for the identification_id field is as I described above, i.e., an incremental value according to the number of person_id's found.
[dbo].[person_identif] (
[person_id] [int] NOT NULL ,
[identification_id] [int] NULL ,
[id_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[class_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[status_code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[good_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[proc_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[id_number] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
person_id | identification_id | id_code | class_code | status_code | good_flag | proc_type | id_number |
279864 |
| MC# |
|
|
|
| P039619 |
390500 |
| MC# |
|
|
|
| 487968 |
314814 |
| MC# |
|
|
|
| 272614 |
354545 |
| MC# |
|
|
|
| 389499 |
354545 |
| MC# |
|
|
|
| 389499 |
365062 |
| MC# |
|
|
|
| 417392 |
316109 |
| MC# |
|
|
|
| 227649 |
340139 |
| MC# |
|
|
|
| 227649 |
317049 |
| MC# |
|
|
|
| 278249 |
348927 |
| MC# |
|
|
|
| 374367 |
April 28, 2005 at 2:38 pm
Ross,
You still need a Primary Key to be able to determine which of the duplicates gets the First id or the second or the thrid ...
For example:
person_id | identification_id | id_code | class_code | status_code | good_flag | proc_type | id_number |
279864 |
| MC# |
|
|
|
| P039619 |
390500 |
| MC# |
|
|
|
| 487968 |
314814 |
| MC# |
|
|
|
| 272614 |
354545 |
| MC# |
|
|
|
| 389499 |
354545 |
| MC# |
|
|
|
| 389499 |
365062 |
| MC# |
|
|
|
| 417392 |
316109 |
| MC# |
|
|
|
| 227649 |
340139 |
| MC# |
|
|
|
| 227649 |
317049 |
| MC# |
|
|
|
| 278249 |
348927 |
| MC# |
|
|
|
| 374367 |
of the two higlighted rows how would you tell which gets 1 and which gets 2 ?
* Noel
April 28, 2005 at 4:49 pm
Noel you are correct about that of course and I should have caught that. This is a database that was generated from a business rules applcation and I am just now trying to build from what I have been presented with. As you can probably see what we are attempting to do is populate the person_id and the identification_id fields and create a concatenated primary key from those. I did go ahead and generate a Row_id field and make it a primary key temporarily in hopes that what work as you suggested but it did not. Any other hints would be appreciated too.
Thanks,
Ross
April 29, 2005 at 1:16 am
Can you try this script ? :
set nocount on
go
if object_id('dbo.person_identif_test') is not null drop table dbo.person_identif_test
go
create table dbo.person_identif_test
(
--id int not null identity(1,1),
person_id int NOT NULL ,
identification_id int not null default(1)
)
go
insert dbo.person_identif_test (person_id) values (1)
insert dbo.person_identif_test (person_id) values (2)
insert dbo.person_identif_test (person_id) values (3)
insert dbo.person_identif_test (person_id) values (4)
insert dbo.person_identif_test (person_id) values (5)
insert dbo.person_identif_test (person_id) values (5)
insert dbo.person_identif_test (person_id) values (5)
insert dbo.person_identif_test (person_id) values (6)
insert dbo.person_identif_test (person_id) values (6)
insert dbo.person_identif_test (person_id) values (6)
insert dbo.person_identif_test (person_id) values (6)
go
alter table dbo.person_identif_test add id int not null identity(1,1)
go
update dbo.person_identif_test
set identification_id = ( select count(*)+1 from dbo.person_identif_test P2 where P1.person_id = P2.person_id and P2.id > P1.id )
from dbo.person_identif_test P1
go
alter table dbo.person_identif_test drop column id
go
select * from dbo.person_identif_test order by person_id,identification_id
go
/* result :
person_id identification_id
1 1
2 1
3 1
4 1
5 1
5 2
5 3
6 1
6 2
6 3
6 4
April 29, 2005 at 11:15 am
Worked liked a charm Bert. I thought initially I would have to use the count function but then thought that I wouldn't have to and all downhill after that. Thank you very much for the help and getting me back on track.
Ross
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply