March 31, 2009 at 12:54 pm
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
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 ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 31, 2009 at 7:55 pm
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
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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply