UPDATE not working

  • 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

               

  • 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.

  • 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

     

     

  • 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

  • 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     

  • 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

     

  • 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