Post reply

Update the table with sequence number

  • March 31, 2009 at 12:54 pm

    #129569

    HI,

    I have to update the destination table with sequence number 1(all rows like 1,1,1,1,1,.....).

    In source table table the seq no is 2,3,4,5....i have to update the destination table with 1 only

    For that i written the query.

    Here i written a stored procedure to get data from different tables and hold that in temp table

    To migrate the data to destination table i created one package.

    In package i used oledb command to update the destination table(row by row updates).

    sourse table is : multiple tables

    Destination table and fields : security_instrument_financing_statement_activity_reporting_identifier

    (sequence_number tinyint,

    workflow_activity_code varchar(10),

    workflow_activity_description varchar(100),

    loan_security_filing_date smalldatetime,

    loan_security_type_description varchar(100),

    security_instrument_type_description varchar(50),

    state_county_flp_code varchar(5),

    ucc_filing_number varchar(16),

    workflow_activity_completion_date smalldatetime,

    workflow_activity_completion_user_name varchar(50),

    workflow_completion_date smalldatetime,

    workflow_completion_user_name varchar(50),

    workflow_creation_date smalldatetime,

    workflow_identifier int,

    farm_loan_customer_identifier int,

    data_effective_start_date smalldatetime,

    data_effective_end_date smalldatetime)

    Here is the query for updates with sequence number 1 :

    declare @SI_financing_statement_activity_reporting_identifier int ,

    @sequence_number int

    select @SI_financing_statement_activity_reporting_identifier=sifsr.security_instrument_financing_statement_activity_reporting_identifier

    from security_instrument_financing_statement_activity_reporting sifsr

    where sifsr.workflow_identifier = [workflow_identifier]

    and sifsr.workflow_activity_code = [workflow_activity_code]

    and sifsr.sequence_number = [sequence_number]

    and sifsr.data_effective_end_date is null

    update security_instrument_financing_statement_activity_reporting

    set data_effective_end_date=getdate()

    where security_instrument_financing_statement_activity_reporting_identifier=@SI_financing_statement_activity_reporting_identifier

    what is the query can i write for sequence number is 1 for al rows.

    Thanks,

    Naidu.

  • March 31, 2009 at 7:27 pm

    #969575

    You just need all occurrences of the field to be 1? Can't believe it's that simple, but here goes:

    update security_instrument_financing_statement_activity_reporting

    set sequence_number = 1

    If I've misunderstood something, please clarify ...

  • March 31, 2009 at 7:55 pm

    #969579

    sequence no is 2(2 means 2 records creted on that activity)one record is updated.then only one record should be update with data_effective_end_date the destination table with seq no 1.

    sequence no is 3(3 means 3 records created on that activity) one record should update with data_effective_end_date the destination table with sequence no 1.

    sequence no is 3 onlyone record is updated then update that record with sequence no 1 the destination table.

  • March 31, 2009 at 8:07 pm

    #969581

    I'm afraid I still do not understand the requirement. Can you post sample data showing

    a) the current data, and

    b) how you would like the data to look after running the UPDATE

    ... just post the fields of interest, to make it easier to read.

    See here[/url] for posting guidelines

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply