aggregate and update

Jan van Veldhuizen · *nix forums beginner Joined: 03 Feb 2005 Posts: 8

Malcolm Dew-Jones · *nix forums Guru Joined: 04 Mar 2005 Posts: 418

Jan van Veldhuizen (jan@van-veldhuizen.nl) wrote:
: 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.

Can't you simply use NVL to convert null sums to zero?

--

This space not for rent.

Jan van Veldhuizen · *nix forums beginner Joined: 03 Feb 2005 Posts: 8

"Jan van Veldhuizen" <jan@van-veldhuizen.nl> wrote in message
news:4202ae5f$0$28980$e4fe514c@news.xs4all.nl...

Jan van Veldhuizen · *nix forums beginner Joined: 03 Feb 2005 Posts: 8

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.

Google