May 17, 2007 at 2:06 pm
In Access, I have a query which adds the value of a column into another one in a second table and stores the result in the second table. This adds up the values of similar rows into one row. I am trying to do a summary table that will be exported to Excel. The query looks like this:
UPDATE tableA
Set Field1=Field1 + tableb.Field2
FROM TableA INNER JOIN tableB ON tableA.key=tableB.key
WHERE criteria
The query does the sum as expected but it appears that in SQL Server it only grabs the last value it encounters and saves that value in Field1. Any idea what is going on? I ended up implementing it as follows:
Select key, sum(field2) as T1
into #temp
from tableB
where criteria
Group by key
update tableA
SET field1=T1
From #temp
May 17, 2007 at 2:26 pm
If table A has a 1 to many relationship with table b, then your first query is actually updating each row in table A that has multiple relationships in table B once fore each relationship in table B. Since each update of the same row over writes the previous update it would appear that you are only getting the "last" value (which is the last update). change your statement to:
UPDATE tableA
Set Field1=Field1 + (select sum(Field2) from TableB b where b.key = a.key)
from tableA a
WHERE criteria
NOTE: If you want to reference tableA in the sub-select you have to add it to the from line, otherwise the from line is not necessary.
James.
May 17, 2007 at 3:13 pm
That would appear to be whats happening. In Access, it takes the value of the field and adds in the next value to it for each row in tableB that matches the criteria. Hence, wouldn't SQL Server do the same? Doesn't the field1 get updated after each addition or only after the query has completed? If it gets updated with each time it encounters the same field, it should accumulate the result not overwrite the result.
May 18, 2007 at 6:43 am
You have to understand what actually occurs when doing this type of update. You original update used a "from" line with an inner join. This results in a "JOINED" (composit) table being created "in memory" (so to speak), that is a composit of the two tables. It is the values from the composit table that are used in all the math and the results of each calculation are then applied back to the table to be updated. Lets use your example in this one:
tableA (key, field1) has the following values:
1, 1
2, 2
3, 3
tableB (key, field2) has the following values:
1,1
1,2
2,1
2,2
2,3
3,1
The resulting "joined" table would be tableC (key, field1, key, field2)
1 1 1 1
1 1 1 2
2 2 2 1
2 2 2 2
2 2 2 3
3 3 3 1
it is from this joined table that the update of the base (tableA) occurs. Therefore while each row in tableA is updated repeatedly, the previously updated value is never taken into consideration via the (field1 = field1 + field2) because field1 and field2 values are comming from the joined/composit table not the base table.
So following the example to its conclsion, tableA is updated as follows:
Update #1 tableA rec#1 field1 is assigned the value (1 + 1) (composit table field1 + field2) = 2
Update #2 tableA rec#1 field1 is assigned the value (1 + 2) (composit table field1 + field2) = 3 (final value)
Update #3 tableA rec#2 field1 is assigned the value (2 + 1) (composit table field1 + field2) = 3
Update #4 tableA rec#2 field1 is assigned the value (2 + 2) (composit table field1 + field2) = 4
Update #5 tableA rec#2 field1 is assigned the value (2 + 3) (composit table field1 + field2) = 5 (final value)
Update #6 tableA rec#3 field1 is assigned the value (3 + 1) (composit table field1 + field2) = 4 (final value)
At least that is my understanding of how it works, if anyone knows different please jump in.
Here is a code example with the same output:
BEGIN
declare @t1 table (key_ int, field1 int)
declare @t2 table (key_ int, field2 int)
insert into @t1 values (1,1)
insert into @t1 values (2,2)
insert into @t1 values (3,3)
insert into @t2 values (1,1)
insert into @t2 values (1,2)
insert into @t2 values (2,1)
insert into @t2 values (2,2)
insert into @t2 values (2,3)
insert into @t2 values (3,1)
select * from @t1 t1 join @t2 t2 on (t1.key_ = t2.key_)
update @t1
set field1 = field1 + field2
from @t1 t1 join @t2 t2 on (t1.key_ = t2.key_)
where 1 = 1 --any where clause you want
select * from @t1
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply