Update problem

[ElPolo]

Hallo Team,
I have a problem with this code (It's an example)

Close Tables all
Create Cursor crsA (key c(1), value n(5))
Insert into crsA (key, value) values ("a",1)
Insert into crsA (key, value) values ("b",1)
Insert into crsA (key, value) values ("c",1)
Insert into crsA (key, value) values ("a",1)
Insert into crsA (key, value) values ("c",1)

Create Cursor crsB (key c(1), total n(5))
Insert into crsB (key) select dist key from crSA

Update crsB set ;
total = crsB.total + crsA.value ;
from crsA ;
where crsA.key = crsB.key

The expected result should be:
a 2
b 1
c 2

but it's not.

This is the result:
a 1
b 1
c 1

Any idea?
Thanks

 

[Chris Miller]

You expect total = crsB.total + crsA.value to run multiple times and so create the cumulative total. Surprise, it's not. Fields don't work like variables. For your need there is SUM as aggregation function. You also don't need to create crsB, you create it as query result.

Close Tables all
Create Cursor crsA (key c(1), value n(5))
Insert into crsA (key, value) values ("a",1)
Insert into crsA (key, value) values ("b",1)
Insert into crsA (key, value) values ("c",1)
Insert into crsA (key, value) values ("a",1)
Insert into crsA (key, value) values ("c",1)

Select key, sum(value) as total from crsA group by key into cursor crsB

Chriss

 

[ElPolo]

I was exactly looking for confirmation.

Fields don't work like variables!

Thanks Chriss

 

[Chris Miller]

Well, fields don't work llike variables is just the truth shortened to the essential thing to know, you can't expect the records to be looped by sql as you would do it.

The sql engine tries to minimize the time it takes by optimizations and therefore it often will not do straight forward what you think it will, like a simple scan of all records of crsA to update a total step by step. The update will only update the target column once and in this case it only puts in the first value from a record of the same key. Then this record is done and not processed again. So it's in detail more about how records are visited.

Chriss