April 17, 2008 at 1:35 pm
I am trying to do a set-based update statement. I have multiple updates for a row in a recordset. I think my problem is in locking/commits, but I'm not sure where to begin. For instance, one record in my table has 3 updates in the recordset. When my update completes, only the first change appears in the table.
Does anyone have any idea how to correct this? This is in an Execute SQL task in an SSIS package used for incremental changes to a database.
thanks,
Kim
April 17, 2008 at 3:12 pm
Can you include the code that does the updates? That would certainly make it easier to figure out what's going on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2008 at 3:40 pm
Here is the actual code (from an execute SQL task in SSIS):
BEGIN TRAN
UPDATE ASSET_EQUIPMENT
SET [UPDTTS] = B.UPDTTS
,[Company_Nbr] = B.Company_Nbr
,[Asset_Nbr] = B.Asset_Nbr
,[Available_State] = B.Available_State
,[Ignition_Status] = B.Ignition_Status
,[Available_Zip] = B.Available_Zip
FROM ASSET_EQUIPMENT A
INNER JOIN [Load_AEQP_Staging] B
ON A.ASSET_NBR = B.ASSET_NBR
GO
COMMIT
The problem is that there are multiple rows in the staging table (updates to be made) for a row in the asset_equipment table. Only the first seems to be updating.
April 18, 2008 at 7:19 am
I guess the question becomes, how do you decide which row to keep the data from?
If the Load_AEQP_Staging table has multiple rows for the same ASSET_NBR, how do you decide which data is the final data for ASSET_EQUIPMENT?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply