[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

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

0
gudidi
Asked:
gudidi
  • 3
  • 2
  • 2
1 Solution
 
Chris MConsulting - Technology ServicesCommented:
Use "COMMIT" right after each update statement, thanks.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
Chris MConsulting - Technology ServicesCommented:
Wait a minute, do you realise that the script you're using to update this table has an update statement referring to the same table i.e DataInterval

Certainly do not expect to have the previous update if it's being "overwritten" by the last update.
Please correct that.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
gudidiAuthor Commented:
i try to use begin transaction and commit transaction and not helping yet
0
 
gudidiAuthor Commented:
you write.

so i do i update only records were not updated before?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
did you see my comment?
0
 
Chris MConsulting - Technology ServicesCommented:
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.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now