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.

update subquery

3 posts in Product Futures Discussion Last posting was on 2003-05-27 12:23:29.0Z
Cris_Vega Posted on 2003-05-22 13:32:40.0Z
From: Cris_Vega
Date: Thu, 22 May 2003 09:32:40 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: update subquery
Message-ID: <F30F16C290445277004A66F485256D2E.004A672185256D2E@webforums>
Lines: 34
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1231
Article PK: 95471

How do I write an update statement which includes a subquery with a group
by statemtent?

For example I tried the following syntax;

UPDATE work_pay_cal_activity_rept
SET active_employee_count =
(SELECT DISTINCT
COUNT(ISNULL(t2.emp_id,'0'))
FROM table_1 t1,
table_2 t2,
work_pay_cal_activity_rept wpcar
WHERE t1.column_1 = wpcar.column_1
AND t1.column_2 = wpcar.column_2
AND t1.column_3 = wpcar.column_3
AND t1.column_4 = wpcar.column_4
AND t1.column_5 = wpcar.column_5

AND t1.payroll_run_type_id = t2.payroll_run_type_id
AND t1.payroll_run_ctrl_id = t2.payroll_run_ctrl_id
AND t1.empl_id = t2.empl_id
AND t1.emp_id = t2e.emp_id
AND t1.pay_pd_id = t2.pay_pd_id
AND t1.pmt_type_code NOT IN ('04','05','06','90')
AND t2.emp_status_code = 'A'
GROUP BY t1.column_1,
t1.column_2
t1.column_3,
t1.column_4,
t2.emp_status_code)

But got error message:
Subquery returned more than 1 value. This is illegal when the subquery
follows =, !=, <, <= , >, >=, or when the subquery is used as an
expression.


Ilya Zvyagin Posted on 2003-05-22 13:56:51.0Z
Reply-To: "Ilya Zvyagin" <masterziv@mail.ru>
From: "Ilya Zvyagin" <masterziv@mail.ru>
References: <F30F16C290445277004A66F485256D2E.004A672185256D2E@webforums>
Subject: Re: update subquery
Date: Thu, 22 May 2003 17:56:51 +0400
Lines: 22
Organization: FCT
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
X-Comment-To: Cris_Vega
FL-Build: Fidolook Express 2001 UIExt. BuildID: 3BC00FAD (7/10/2001 12:17:49).
Message-ID: <1053611811.949120@gatekeeper.fct.ru>
Cache-Post-Path: gatekeeper.fct.ru!unknown@dream.int.fct.ru
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: gatekeeper.fct.ru 212.113.103.2
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1232
Article PK: 95469

Hello, Cris_Vega!
You wrote on Thu, 22 May 2003 09:32:40 -0400:

CV> How do I write an update statement which includes a subquery with a group by statemtent?

CV> UPDATE work_pay_cal_activity_rept
CV> SET active_employee_count =
....
CV> AND t2.emp_status_code = 'A'
CV> GROUP BY t1.column_1, t1.column_2 t1.column_3,
CV> t1.column_4, t2.emp_status_code)

There should not be GROUP BY here, use corelated subquery instead.
You use wrong query and with ASE's non-ANSI GROUP BY's you
got this error.

--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port
E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
ICQ UID: 29427861(MasterZIV)


Cris_Vega Posted on 2003-05-27 12:23:29.0Z
From: Cris_Vega
Date: Tue, 27 May 2003 08:23:29 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: update subquery
Message-ID: <31E9B79E1AE03A990044116585256D33.0053732885256D2E@webforums>
References: <F30F16C290445277004A66F485256D2E.004A672185256D2E@webforums> <1053611811.949120@gatekeeper.fct.ru>
Lines: 22
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1240
Article PK: 95479

Ilya,
Thank you for responding to my post.

I'm having trouble with the syntax of the correlated subquery.
Specifically, how to get a record count back to the outer query.
Here is an example.

Update table_name_1
Set table_name_1.column_1 = -- how do I get record count?
(Select Distinct
COUNT(ISNULL(table_name_2.emp_id,'0')
From table_name_2
Where table_name_2.column_3 = 'A'
Group by table_name_2.column_1,
table_name_2.column_2)
From table_name_2
Where table_name_2.column_1 = 'A'

Can you provide a sample of the proper syntax?
Thanks in advance for your input.