"Jan van Veldhuizen" <jan_at_van-veldhuizen.nl> wrote in message
news:4202ae5f$0$28980$e4fe514c_at_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) mysum from test group by name)
vw
> where vw.name = counters.name)
> where exists
> (select null from (select name, sum(val) mysum 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:08:50 CST
