Can update accumulate?

I need to write an UPDATE statement that adds to a field from data in
another table. Can someone help? below is sample:
UPDATE TableA
SET Total = Total + TableB.Amount
FROM TableB JOIN TableA ON TableB.EmpNo = TableA.EmpNo
WHERE TableB.PrdYr = 2005
When I do this, it does not add in the incremented Total field and I end up
with the last TableB.Amount value.
Thanks.
David>> I need to write an UPDATE statement that adds to a field from data in
Yes, but you will have to provide sufficient information for others to
understand your problem. Pl. read www.aspfaq.com/5006 and post your DDLs,
sample data & expected results
Anith|||Try this, it will keep a running total in TableA each time the query is
run. If this is going to be run and needs all of the values to start
out 0 (no running total), then remove the 'Total + ' part of the query.
UPDATE TableA
SET Total = Total +
( SELECT ISNULL(SUM(TableB.Amount),0)
FROM TableB
WHERE TableB.PrdYr = 2005
and TableB.EmpNo = TableA.EmpNo
)
Kalvin|||David,
An UPDATE statement will only make one assignment
to each column. UPDATE .. FROM is a T-SQL extension
to standard SQL that allows poorly defined statements, and
while it can be handy, it can also cause confusion. I wish an
error were raised in situations like this, but that's not the case.
To do what you want, you probably need something like
update TableA set
Total = Total + (
select sum(TableB.Amount)
from TableB
where TableB.EmpNo = TableA.EmpNo
and TableB.PrdYr = 2005
)
Steve Kass
Drew University
David wrote:

>I need to write an UPDATE statement that adds to a field from data in
>another table. Can someone help? below is sample:
>UPDATE TableA
>SET Total = Total + TableB.Amount
>FROM TableB JOIN TableA ON TableB.EmpNo = TableA.EmpNo
>WHERE TableB.PrdYr = 2005
>When I do this, it does not add in the incremented Total field and I end up
>with the last TableB.Amount value.
>Thanks.
>David
>
>|||Kalvin caught one thing I didn't. This needs either COALESCE
or a WHERE condition on the update, to avoid NULLing out
Total values when there's no match in TableB. Here's a WHERE
condition that ought to do it.
update TableA set
Total = Total + (
..
)
where exists (
select *
from TableB
where TableB.EmpNo = TableA.EmpNo
and TableB.PrdYr = 2005
and TableB.Amount is not null
)
SK
Steve Kass wrote:
> David,
> An UPDATE statement will only make one assignment
> to each column. UPDATE .. FROM is a T-SQL extension
> to standard SQL that allows poorly defined statements, and
> while it can be handy, it can also cause confusion. I wish an
> error were raised in situations like this, but that's not the case.
> To do what you want, you probably need something like
> update TableA set
> Total = Total + (
> select sum(TableB.Amount)
> from TableB
> where TableB.EmpNo = TableA.EmpNo
> and TableB.PrdYr = 2005
> )
>
> Steve Kass
> Drew University
> David wrote:
>|||1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
2) Would you like to learn REAL SQL or only some proprietary kludges
that have unpredicatable results, as you have posted?
Did you actually split out a year as a temporal column'!! Surely not
!
Why don't you know that column and field are **totally** different?
Why don't you know that there is no such thing as a generic, magical
"amount" -- it has to be the amount of something. Have you ever had a
BASIC -- repeat BASIC in capital letters -- data modeling class?
You can probably get enough kludges in a newsgroup to slip past your
boss unitl you get to the next job to screw up them too.
I got an email tonight form a kid who volunteered to do a DB for an
African Relief agency and seriously screwed it up. I got the consult
after things got messed up and I posted this in some newsgroups as an
example. I guess he found me via those postings.
I know his design crippled some children; I am not sure about causing
deaths and a part of me does not want to know. Please care enough not
to do that. To other people. To other people.sql

Can Timestamp column value overflow?

Hi,
I am having a table with a timestamp column. The data in this table is
assumed to accumulate over a period of time. On an avarage every second a
row is inserted into this table.
I using time stamp column for two reasons:
- Ensure that the value of timestamp is ever incrementing.
- Time stamp value should be unique.
Issue: Is there any probability of timestamp getting overflow over a period
of time? If there is a overflow what is the behavior of SQL Server in such
cases?
If there can not be overflow, then how SQL Server manages to generate ever
incrementing unique value for 8 bytes size column.
A bigint Identity column gives arithmetic overflow error if value exceeds
the maximum range.
Thanks in advance.
Pushkar> A bigint Identity column gives arithmetic overflow error if value exceeds
> the maximum range.
I don't remember the exact calculation, but I think you have to do 10,000
inserts a second for 122 years or something like that, to exceed the upper
bound of a BIGINT. So, you're probably safe. If you have anything other
than this table in the database, you're going to run out of available disk
space on the planet before you use up all of the unique, ever-incrementing
BIGINT values available to you.
Just my opinion.
A|||Consider using an identity column of datatype 4 byte int.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:%23fMDNZ0UGHA.5044@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am having a table with a timestamp column. The data in this table is
> assumed to accumulate over a period of time. On an avarage every second a
> row is inserted into this table.
> I using time stamp column for two reasons:
> - Ensure that the value of timestamp is ever incrementing.
> - Time stamp value should be unique.
> Issue: Is there any probability of timestamp getting overflow over a
> period of time? If there is a overflow what is the behavior of SQL Server
> in such cases?
> If there can not be overflow, then how SQL Server manages to generate ever
> incrementing unique value for 8 bytes size column.
> A bigint Identity column gives arithmetic overflow error if value exceeds
> the maximum range.
>
> Thanks in advance.
> Pushkar
>|||> Issue: Is there any probability of timestamp getting overflow over a period of time? If t
here is a
> overflow what is the behavior of SQL Server in such cases?
The way I remember it:
Even when this was internally a 6 byte size (some earlier version), you coul
d do something like 100
transactions per second for over 100 years before overflow. And it is now 8
byte.
And well before such a theoretic overflow would happen, SQL Server would log
an error and not permit
any more updates in the database, would such a (theoretic) overflow occur. T
his I recall reading in
Books Online, some earlier version.
OK, did some calculations. See code below. If you do 1 transaction per secon
d, it will "overflow"
after 146,235,604,338 years. Or, put another way, if you do 1 million transa
ctions per second, it
will overflow after 146,235 years. Say you plan for a life span of 100 years
for the database, you
would have to do 1 billion transactions per second. TSQL calculations:
DECLARE @.noPossibleValues numeric(38,0)
DECLARE @.secondsPerYear int
SET @.noPossibleValues = POWER(CAST(2 AS numeric(38,0)), 62)
SET @.secondsPerYear = 60*60*24*365
SELECT @.noPossibleValues/@.secondsPerYear AS YearsIfOneTransactionPerSecond
SELECT @.noPossibleValues/(CAST(@.secondsPerYear AS bigint) * 1000000) AS
YearsIfMillionTransactionsPerSecond
And here is some old text that I found on the subject:
"Maximum Value
--
Timestamps increase until the maximum value that can be stored in 6
bytes (2**48) is reached (8 bytes, nowadays). When this maximum is reached,
the database
will not permit any more updates.
A 935 warning message is generated when there are only 1,000,000
timestamp values left in the database.
The only way to start over is to copy out all of the data with BCP
and to re-create the database; dumping and restoring will not help.
This is not a major concern because at 100 transactions per second,
2**48 will not wrap for more than 100 years."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:%23fMDNZ0UGHA.5044@.TK2MSFTNGP09.p
hx.gbl...
> Hi,
> I am having a table with a timestamp column. The data in this table is ass
umed to accumulate over
> a period of time. On an avarage every second a row is inserted into this
table.
> I using time stamp column for two reasons:
> - Ensure that the value of timestamp is ever incrementing.
> - Time stamp value should be unique.
> Issue: Is there any probability of timestamp getting overflow over a perio
d of time? If there is a
> overflow what is the behavior of SQL Server in such cases?
> If there can not be overflow, then how SQL Server manages to generate ever
incrementing unique
> value for 8 bytes size column.
> A bigint Identity column gives arithmetic overflow error if value exceeds
the maximum range.
>
> Thanks in advance.
> Pushkar
>|||Oops, I did 2**62 instead of 2**64. Means my numbers were on the conservativ
e side... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:e4gVkw0UGHA.1160@.TK2MSFTNGP09.phx.gbl...
> The way I remember it:
> Even when this was internally a 6 byte size (some earlier version), you co
uld do something like
> 100 transactions per second for over 100 years before overflow. And it is
now 8 byte.
> And well before such a theoretic overflow would happen, SQL Server would l
og an error and not
> permit any more updates in the database, would such a (theoretic) overflow
occur. This I recall
> reading in Books Online, some earlier version.
> OK, did some calculations. See code below. If you do 1 transaction per sec
ond, it will "overflow"
> after 146,235,604,338 years. Or, put another way, if you do 1 million tran
sactions per second, it
> will overflow after 146,235 years. Say you plan for a life span of 100 yea
rs for the database, you
> would have to do 1 billion transactions per second. TSQL calculations:
> DECLARE @.noPossibleValues numeric(38,0)
> DECLARE @.secondsPerYear int
> SET @.noPossibleValues = POWER(CAST(2 AS numeric(38,0)), 62)
> SET @.secondsPerYear = 60*60*24*365
> SELECT @.noPossibleValues/@.secondsPerYear AS YearsIfOneTransactionPerSecond
>
> SELECT @.noPossibleValues/(CAST(@.secondsPerYear AS bigint) * 1000000) AS
> YearsIfMillionTransactionsPerSecond
>
> And here is some old text that I found on the subject:
> "Maximum Value
> --
> Timestamps increase until the maximum value that can be stored in 6
> bytes (2**48) is reached (8 bytes, nowadays). When this maximum is reached
, the database
> will not permit any more updates.
> A 935 warning message is generated when there are only 1,000,000
> timestamp values left in the database.
> The only way to start over is to copy out all of the data with BCP
> and to re-create the database; dumping and restoring will not help.
> This is not a major concern because at 100 transactions per second,
> 2**48 will not wrap for more than 100 years."
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:%23fMDNZ0UGHA.5044@.TK2MSFTNGP09.phx.gbl...
>|||Thanks a lot. Now I can safely use it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uurqw78UGHA.4660@.tk2msftngp13.phx.gbl...
> Oops, I did 2**62 instead of 2**64. Means my numbers were on the
> conservative side... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:e4gVkw0UGHA.1160@.TK2MSFTNGP09.phx.gbl...
>sql