aggregate and update

From: Jan van Veldhuizen <jan_at_van-veldhuizen.nl>
Date: Fri, 4 Feb 2005 00:04:56 +0100
Message-ID: <4202ae5f$0$28980$e4fe514c@news.xs4all.nl>

I am trying to update a table with aggregate values without creating a view. I am having more complicated tables, but I will show what am doing with some simple tables:

create table counters (name varchar(10), cnt int); create table test (name varchar(10), val int);

insert into counters values('john', 0);
insert into counters values('peter', 0);
insert into counters values('david', 0);

insert into test values('john', 1);

insert into test values('john', 3);
insert into test values('peter', 10);

I want to calculate the sum of the val column and store it in the cnt column of the counters table.
And I want the cnt value of David to remain zero, and not nullified because it does not exists in the test table.

If a create a view it is simple:

create view myview as select name, sum(val) mysum from test group by name;

update counters set cnt = (select mysum from myview where myview.name = counters.name)
where exists (select null from myview where myview.name = counters.name)

But now without the view:

update counters set cnt =
(select mysum from (select name, sum(val) som from test group by name) vw
where vw.name = counters.name)
where exists
(select null from (select name, sum(val) som from test group by name) vw
where vw.name = counters.name)

My question is: is this inefficient because it is creating the view vw twice?

The advantage is: I can use this syntax in both Oracle and SqlServer, so I can avoid to write separate statements for both databases. I have an application with sql embedded in the source, so I am trying to keep the sql as universal as possible. Received on Thu Feb 03 2005 - 17:04:56 CST