asked on

2 Update command at the same block failed

Hi Experts

i try to update a table from 2 other tables.
when i do it only from one table it is OK.
when i try to Update from boath tables one after one,
i receive only the last update. the previous update is goen.

is there a problem in my script? i tried to swiched the scripts and allways only the last one update the table.

please help

10x

/* part in hist and part in agg */
if (@FromDt<=@MinDtHist) and (@ToDt>=@MaxDtAgg)
begin

	update DataInterval
	set CountEnter=(select COUNT(1)from HISTORY_DATA where STORE_ID=@StoreId and ACTION_ID=1
	and HISTORY_DATA.DATE_RECORD=DataInterval.FromDate and 
	(HISTORY_DATA.TIME_RECORD between DataInterval.intervalFrom and DataInterval.intervalTo ))
	
	update DataInterval
	
	set CountEnter=(select SUM(ACTION_1) from HISTORY_DATA_AGGREGATE 
	where STORE_ID=@StoreId
	and DataInterval.FromDate=HISTORY_DATA_AGGREGATE.REC_DATE and 
	HISTORY_DATA_AGGREGATE.TM_FROM>=DataInterval.intervalFrom and HISTORY_DATA_AGGREGATE.		    TM_TO<=DataInterval.intervalTo)	
end

Open in new window

Chris M

Use "COMMIT" right after each update statement, thanks.

Guy Hengel [angelIII / a3]

I presume that the second statment is overwriting the first update's changes ...

you might want "add"

/* part in hist and part in agg */
if (@FromDt<=@MinDtHist) and (@ToDt>=@MaxDtAgg)
begin

	update DataInterval
	set CountEnter=(select COUNT(1)from HISTORY_DATA where STORE_ID=@StoreId and ACTION_ID=1
	and HISTORY_DATA.DATE_RECORD=DataInterval.FromDate and 
	(HISTORY_DATA.TIME_RECORD between DataInterval.intervalFrom and DataInterval.intervalTo ))
	
	update DataInterval
	
	set CountEnter= isnull(CountEnter,0) + (select SUM(ACTION_1) from HISTORY_DATA_AGGREGATE 
	where STORE_ID=@StoreId
	and DataInterval.FromDate=HISTORY_DATA_AGGREGATE.REC_DATE and 
	HISTORY_DATA_AGGREGATE.TM_FROM>=DataInterval.intervalFrom and HISTORY_DATA_AGGREGATE.TM_TO<=DataInterval.intervalTo)	
end

Open in new window

ASKER CERTIFIED SOLUTION

Chris M

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gudidi

ASKER

i try to use begin transaction and commit transaction and not helping yet

gudidi

ASKER

you write.

so i do i update only records were not updated before?

Guy Hengel [angelIII / a3]

did you see my comment?

Chris M

Your first update query updated the details of "CountEnter" field in "DataInterval" table but right after, your second update statement set in and updated hence basically overwriting what the first update did.

By the time you ran the SELECT statement to view what you have, you could only see the details of the second update. Since you're updating the same field in the same table, like I said before, the second update "overwrites" what the first one did.