Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

table update

6 posts in Windows NT Last posting was on 2000-10-24 01:37:24.0Z
aplmr30 Posted on 2000-09-12 15:41:32.0Z
Message-ID: <39BE4EAC.B1880D77@netscapeonline.co.uk>
Date: Tue, 12 Sep 2000 16:41:32 +0100
From: aplmr30 <aplmr30@netscapeonline.co.uk>
Organization: Netscape Online member
X-Mailer: Mozilla 4.6 [en-gb]C-CCK-MCD NetscapeOnline.co.uk (Win95; I)
X-Accept-Language: en-GB,en
MIME-Version: 1.0
Subject: table update
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 26
NNTP-Posting-Host: useran92.netscapeonline.co.uk 62.125.133.110
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1583
Article PK: 1089059

I have a table with 3 columns

col_a char(2) not null,
col_b tinyint not null,
col_c tinyint null

The table is populated with

col_a col_b col_c
AB 1 null
AC 2 null
AD 3 null
AE 4 null
AB 5 null
AC 6 null
AD 7 null
AB 8 null


I want to update col_c with the sum of each of the distinct values in
col_a to give the following results.

col_a col_b col_c
AB 1 14
AC 2 8
AD 3 10
AE 4 4
AB 5 14
AC 6 8
AD 7 10
AB 8 14

I want to do this in one statement without adding temporary tables and
without using a cursor or any type of looping. How can I do this ?


sverre tvedt Posted on 2000-10-13 12:05:53.0Z
From: "sverre tvedt" <a@b>
References: <39BE4EAC.B1880D77@netscapeonline.co.uk>
Subject: Re: table update
Date: Fri, 13 Oct 2000 14:05:53 +0200
Lines: 49
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <9nhe79QNAHA.267@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 193.214.127.151
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1576
Article PK: 1089053

Assuming that you meant to say "sum of each of the distinct values in
col_b" ( and not col_a ) :


update Xtable t1 set t1.col_c = (select sum(t2.col_b) from Xtable t2 where
t2.col_a= t1.col_a)

aplmr30 <aplmr30@netscapeonline.co.uk> wrote in message
news:39BE4EAC.B1880D77@netscapeonline.co.uk...
> I have a table with 3 columns
>
> col_a char(2) not null,
> col_b tinyint not null,
> col_c tinyint null
>
> The table is populated with
>
> col_a col_b col_c
> AB 1 null
> AC 2 null
> AD 3 null
> AE 4 null
> AB 5 null
> AC 6 null
> AD 7 null
> AB 8 null
>
>
> I want to update col_c with the sum of each of the distinct values in
> col_a to give the following results.
>
> col_a col_b col_c
> AB 1 14
> AC 2 8
> AD 3 10
> AE 4 4
> AB 5 14
> AC 6 8
> AD 7 10
> AB 8 14
>
> I want to do this in one statement without adding temporary tables and
> without using a cursor or any type of looping. How can I do this ?
>


GMSAYLORIII Posted on 2000-10-13 22:43:04.0Z
From: "GMSAYLORIII" <GMSAYLORIII@email.msn.com>
References: <39BE4EAC.B1880D77@netscapeonline.co.uk> <9nhe79QNAHA.267@forums.sybase.com>
Subject: Re: table update
Date: Fri, 13 Oct 2000 18:43:04 -0400
Lines: 95
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <gTYdqjWNAHA.280@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 1Cust235.tnt2.baltimore.md.da.uu.net 63.24.127.235
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1573
Article PK: 1089049

I am gathering that you wnat the table to llok like the output of this query

select col1, col2,sum(col2)
from Tbl
group by col1

Since you cannot do group by in update, you will need to use either a #temp
table or a cursor.( sad but true)

Once you do that you can use a trigger (below is a slimmed down model for
new inserts)
You will need one for each update of col1 or 2
insert of col1 or 2
delete of col1 or 2

This method means never repeating the longer running process although it
could have a small slowing per manipulation which can add up on high use
OLTP.

create trigger UpdSum_tr
on Tbl
for insert
as
declare @sum int

select @sum = sum(a.col2)
from Tbl a, inserted
where a.col1 = inserted.col1
group by a.col1
update Tbl
set a.col3 = @sum
from Tbl a, inserted
where
a.col1 = inserted.col1
if @@error != 0
begin
<This is up to you!, I like an exception table and raiserror>
end

go

sverre tvedt <a@b> wrote in message
news:9nhe79QNAHA.267@forums.sybase.com...
> Assuming that you meant to say "sum of each of the distinct values in
> col_b" ( and not col_a ) :
>
>
> update Xtable t1 set t1.col_c = (select sum(t2.col_b) from Xtable t2 where
> t2.col_a= t1.col_a)
>
>
>
>
> aplmr30 <aplmr30@netscapeonline.co.uk> wrote in message
> news:39BE4EAC.B1880D77@netscapeonline.co.uk...
> > I have a table with 3 columns
> >
> > col_a char(2) not null,
> > col_b tinyint not null,
> > col_c tinyint null
> >
> > The table is populated with
> >
> > col_a col_b col_c
> > AB 1 null
> > AC 2 null
> > AD 3 null
> > AE 4 null
> > AB 5 null
> > AC 6 null
> > AD 7 null
> > AB 8 null
> >
> >
> > I want to update col_c with the sum of each of the distinct values in
> > col_a to give the following results.
> >
> > col_a col_b col_c
> > AB 1 14
> > AC 2 8
> > AD 3 10
> > AE 4 4
> > AB 5 14
> > AC 6 8
> > AD 7 10
> > AB 8 14
> >
> > I want to do this in one statement without adding temporary tables and
> > without using a cursor or any type of looping. How can I do this ?
> >
>
>


sverre tvedt Posted on 2000-10-14 13:37:26.0Z
From: "sverre tvedt" <a@b>
References: <39BE4EAC.B1880D77@netscapeonline.co.uk> <9nhe79QNAHA.267@forums.sybase.com> <gTYdqjWNAHA.280@forums.sybase.com>
Subject: Re: table update
Date: Sat, 14 Oct 2000 15:37:26 +0200
Lines: 37
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <BPr2xVeNAHA.251@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 193.214.127.151
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1572
Article PK: 1089050


> select col1, col2,sum(col2)
> from Tbl
> group by col1
>

This query is illegal, col2 is not a member of the group.

I forgot one of the conditions, namely that col_c should contain the sum of
'distinct' values in col_b. (E.g. the value '8' should only contribute once
to the sum). It that case the update becomes :

update Xtable t1 set t1.col_c = (select sum(distinct t2.col_b) from Xtable
t2 where t2.col_a= t1.col_a)

The statement can be modified to act on a subset of the table :

update Xtable t1 set t1.col_c = (select sum(distinct t2.col_b) from Xtable
t2 where t2.col_a= t1.col_a)
where < any condition limiting the set of records in XTable>

The added where clause can be written to act on only records affected by
updates, inserts or deletes, so that it may be further elaborated to be used
in a trigger firing each time the table is modified - if it is a wish that
the table is continually updated without having to re-compute the complete
table (can be nice if it is a large table).

Example of a where clause if update is performed from inside of a delete
trigger on table XTable:

update Xtable t1 set t1.col_c = (select sum(distinct t2.col_b) from Xtable
t2 where t2.col_a= t1.col_a)
where t1.col_a in (select col_a from deleted)


Rob Verschoor Posted on 2000-10-14 16:50:39.0Z
Reply-To: "Rob Verschoor" <rob@sypron.nl>
From: "Rob Verschoor" <rob@sypron.nl>
References: <39BE4EAC.B1880D77@netscapeonline.co.uk> <9nhe79QNAHA.267@forums.sybase.com> <gTYdqjWNAHA.280@forums.sybase.com> <BPr2xVeNAHA.251@forums.sybase.com>
Subject: Re: table update
Date: Sat, 14 Oct 2000 18:50:39 +0200
Lines: 63
Organization: Sypron B.V.
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <CGFEwDgNAHA.267@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: i0620.pvu.euronet.nl 194.134.166.110
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1571
Article PK: 1089045


"sverre tvedt" <a@b> wrote in message
news:BPr2xVeNAHA.251@forums.sybase.com...
>
>
> > select col1, col2,sum(col2)
> > from Tbl
> > group by col1
> >
>
> This query is illegal, col2 is not a member of the group.
>

Not quite: the syntax is not valid in ANSI SQL, but it *is* valid in
T-SQL. However, we should assume the original poster inadvertantly
wrote a query contianing a functional error, because the results will
probably not be what is expected (the manula says somewhere that the
results of this type of query "may be difficult to interpret" -- which
means it's a mess)

Rob V.

--
If you don't want to use X Windows, you could try "sybinit4ever", a
free tool which uses only an ASCII interface to create a new ASE
server. It can be downloaded from http://www.sypron.nl/si4evr.html

See the
ASE reference manual / System Administration Guide / Transact-SQL
user's guide
This / These books can be viewed or downloaded as PDF files from the
Sybase website http://sybooks.sybase.com . For more details how to get
there, see http://www.sypron.nl/sybbooks.html .

For more information on the background of this problem, as well as a
solution, see http://www.sypron.nl/idgaps.html .

... syntax and description are in the ASE Quick Reference Guide
Supplement, which you can download from
http://www.sypron.nl/ase_qref.html .

To use dynamic SQL, you need at least ASE version 12.0, which has the
"execute immediate" feature for this. In earlier versions of ASE you
can simulate some types of dynamic SQL though; for more information,
see http://www.sypron.nl/dynsql.html and
http://www.sypron.nl/dynsqlcis.html .

HTH,

Rob
----------------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.0
Certified Sybase Professional DBA/SQL Developer for ASE 11.5
Certified DBA/Performance & Tuning Specialist for Sybase System 11

email mailto:rob@sypron.nl.*No*Spam*Please*
WWW http://www.sypron.nl
snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
----------------------------------------------------------------------


aplmr30 Posted on 2000-10-24 01:37:24.0Z
Message-ID: <39F4E7D3.87B67A7D@netscapeonline.co.uk>
Date: Tue, 24 Oct 2000 02:37:24 +0100
From: aplmr30 <aplmr30@netscapeonline.co.uk>
Organization: Netscape Online member
X-Mailer: Mozilla 4.6 [en-gb]C-CCK-MCD NetscapeOnline.co.uk (Win95; I)
X-Accept-Language: en-GB,en
MIME-Version: 1.0
Subject: Re: table update
References: <39BE4EAC.B1880D77@netscapeonline.co.uk> <9nhe79QNAHA.267@forums.sybase.com> <gTYdqjWNAHA.280@forums.sybase.com> <BPr2xVeNAHA.251@forums.sybase.com> <CGFEwDgNAHA.267@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 60
NNTP-Posting-Host: userak35.netscapeonline.co.uk 62.125.132.10
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1551
Article PK: 1089028

cheers for the answers - you all confirmed what I already thought.

Rob Verschoor wrote:

> "sverre tvedt" <a@b> wrote in message
> news:BPr2xVeNAHA.251@forums.sybase.com...
> >
> >
> > > select col1, col2,sum(col2)
> > > from Tbl
> > > group by col1
> > >
> >
> > This query is illegal, col2 is not a member of the group.
> >
>
> Not quite: the syntax is not valid in ANSI SQL, but it *is* valid in
> T-SQL. However, we should assume the original poster inadvertantly
> wrote a query contianing a functional error, because the results will
> probably not be what is expected (the manula says somewhere that the
> results of this type of query "may be difficult to interpret" -- which
> means it's a mess)
>
> Rob V.
>
> --
> If you don't want to use X Windows, you could try "sybinit4ever", a
> free tool which uses only an ASCII interface to create a new ASE
> server. It can be downloaded from http://www.sypron.nl/si4evr.html
>
> See the
> ASE reference manual / System Administration Guide / Transact-SQL
> user's guide
> This / These books can be viewed or downloaded as PDF files from the
> Sybase website http://sybooks.sybase.com . For more details how to get
> there, see http://www.sypron.nl/sybbooks.html .
>
> For more information on the background of this problem, as well as a
> solution, see http://www.sypron.nl/idgaps.html .
>
> ... syntax and description are in the ASE Quick Reference Guide
> Supplement, which you can download from
> http://www.sypron.nl/ase_qref.html .
>
> To use dynamic SQL, you need at least ASE version 12.0, which has the
> "execute immediate" feature for this. In earlier versions of ASE you
> can simulate some types of dynamic SQL though; for more information,
> see http://www.sypron.nl/dynsql.html and
> http://www.sypron.nl/dynsqlcis.html .
>
> HTH,
>
> Rob
> ----------------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 12.0
> Certified Sybase Professional DBA/SQL Developer for ASE 11.5
> Certified DBA/Performance & Tuning Specialist for Sybase System 11
>
> email mailto:rob@sypron.nl.*No*Spam*Please*
> WWW http://www.sypron.nl
> snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> ----------------------------------------------------------------------