Mulitple Row update how

[LeighDreyer]

Hi I am trying to update multiple rows from one table but keep getting the error "ORA-01427: single-row subquery returns more than one row" how can I do it here is my code statement I am trying

UPDATE GPCT_PIMS.GPCT_WARDCOUNT SET (MALE_UNDER_65) =

(SELECT COUNT(REF_DATE) AS "MALE_UNDER_65"

FROM GPCT_PIMS.GPCT_WARDCOUNT

WHERE SEX = 'M'
AND
OVER_65 = 'N'

GROUP BY WARD_NAME,
REF_DATE,
SEX,
OVER_65)

 

[lewisp]

The GROUP BY in your subquery is causing it to return more than one row. You need to rewrite your update similar to this:

UPDATE GPCT_PIMS.GPCT_WARDCOUNT a SET (a.MALE_UNDER_65) =
(SELECT COUNT(b.REF_DATE) AS "MALE_UNDER_65"
FROM GPCT_PIMS.GPCT_WARDCOUNT b
WHERE b.SEX = 'M'
AND b.OVER_65 = 'N'
AND b.WARD_NAME = a.WARD_NAME
AND b.REF_DATE = a.REF_DATE)

 

[LeighDreyer]

Ok but where are you getting the a. reference from I am only using one table to pull data from

UPDATE GPCT_PIMS.GPCT_WARDCOUNT a SET (a.MALE_UNDER_65) =
(SELECT COUNT(b.REF_DATE) AS "MALE_UNDER_65"
FROM GPCT_PIMS.GPCT_WARDCOUNT b
WHERE b.SEX = 'M'
AND b.OVER_65 = 'N'
AND b.WARD_NAME = a.WARD_NAME
AND b.REF_DATE = a.REF_DATE)

 

[LeighDreyer]

I tried that and got missing Expression at the WHERE statement any ideas ? Ignore my last post I understand what i happening but still get error when trying this ??