Hi,

We need to change sql from DBISAM to ElevateDB, and we are looking for the best way to update a table with calculated values from another table.

This is how we do it in DBISAM today

UPDATE Items SET
StockQty_1=IFNULL(StockQty_1,b.QtyDiff,StockQty_1+b.QtyDiff),
StockVal_1=IFNULL(StockVal_1,b.AccumVal,StockVal_1+b.AccumVal)
FROM Items
INNER JOIN "buffer" b ON "No"=b.ItemNo AND (b.QtyDiff<>0 OR b.ValDiff<>0);

My experience from sql server is to do something like this:

update i
set i.stockqty1 = b.qtydiff
from items as i
inner join
buffer as b
on i.no = b.itemid
where
(b.qtydiff<>0) or (b.valdiff<>0)

but none of those syntaxs works with elevatedb, the only way we can do the same, is to do something like this:

update items
set stockqty_1 = (select IFNULL(StockQty_1,b.QtyDiff,StockQty_1+b.QtyDiff) from buffer b where no = b.itemno AND (b.QtyDiff<>0 OR b.ValDiff<>0)),
stockval_1 = (select IFNULL(StockVal_1,b.AccumVal,StockVal_1+b.AccumVal) from buffer b where no = b.itemno AND (b.QtyDiff<>0 OR b.ValDiff<>0))

Do anyone knows a better way to make updates like this in ElevateDB. I think the update will cost extra because we have to lookup each value, that we want to assign to the update.


Kind Regards


Peter Beyer
Admind

PeterBeyer


As far as I know UPDATE and DELETE both now use subselects rather than joins. Gave me a lot of grief when I first switched over - I'd just managed to force the JOIN syntax into my aging brain and then had to remove it and insert subselects instead

The only thing I can suggest for speed is to profile the subselect independent of the update and optimise as best you can.

Roy Lambert

Peter,

<< Do anyone knows a better way to make updates like this in ElevateDB. I
think the update will cost extra because we have to lookup each value, that
we want to assign to the update >>

It’s not the lookup that's expensive, it’s the lack of a WHERE clause on the
items table resulting in all rows being updated.

Normally, when you start using UPDATE queries like this a lot, it indicates
an issue with the database design.  More specifically, the normalization of
the tables.  One shouldn't often have to update data in one table by
directly using data from another table, with the key word here being
"often".  There are, of course, situations where one may need to do so, but
they are normally infrequent and one-off situations, such as a mass-change
to primary keys, etc.

Tim Young
Elevate Software
www.elevatesoft.com

Dear Peter Beyer,

Note that for update statements the following syntax does work:

UPDATE <TableName>
SET
(Column1, Column2) =
(SELECT Column1, Column2 FROM <TableName>)

i.e. you can replace the multiple SELECT statements in your existing SQL with a single statement, provided that the columns you are updating and select statement are bracketed as shown.

I believe this would make your lives a bit easier, and probably also speed up the query.

Hi Adam,

Sorrry for my late reply, I have being on a short vercation.
But your reply have really helped us, it was something like this we were looking for - thank you

How did you find out, that the syntax works like this. Are there places, where we can look for examples for the SQL 2003 standard, that you/others can suggests ?

Kindly Regards



Peter
Admind

PeterBeyer

It is partly a mind-set thing. Remember SQL is always and everywhere about SETs. Listing things in brackets works in a lot of places in SQL, as this is how sets are represented.

For example you can use it in JOIN clauses and WHERE clauses ... complicated to begin with, but when you get the hang of it pretty easy.

I would recommend the "SQL For Smarties" by Joe Celko. As with many coder-writers he has a bit of an annoying style (!) ... but there are lots of mind-expanding moments in the books. The books are on Amazon and they are expensive. There are lots of websites which have details of the work, and PDFs.

Adam,

<< I believe this would make your lives a bit easier, and probably also
speed up the query. >>

Yes, it will save one additional sub-query execution per row being updated.

Tim Young
Elevate Software
www.elevatesoft.com

Peter,

<< How did you find out, that the syntax works like this. Are there places,
where we can look for examples for the SQL 2003 standard, that you/others
can suggests ? >>

You can find more information on row value constructors here:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Row_Value_Constructors

Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

I can see that we should have investigate a little bit more in the manual.

Thanks

Peter