single-row subquery returns more

[bond20]

I'm getting the following error when this statement is run:
ERROR : File 'exec_stored.c', function 'nInvokeStoredProcedure', line 391
Error : Stored procedure reports error 20001, 'post_each_file : 300 - update where already received ORA-01427: single-row subquery returns more
than one row'.


UPDATE
f_flash_daily_feed f
SET
(
f.otc_incl_vat,
f.levy_items,
f.non_levy_items,
f.pills_items
)
=
(
SELECT DISTINCT
(s.otc_incl_vat + f.otc_incl_vat),
(s.levy_items + f.levy_items),
(s.non_levy_items + f.non_levy_items),
(s.pills_items + f.pills_items)

FROM
s_flash_daily_feed s
WHERE
s.date_id = f.date_id and
s.branch_id = f.branch_id)
WHERE
EXISTS
(
SELECT
1
FROM
s_flash_daily_feed
WHERE
date_id = f.date_id and
branch_id = f.branch_id and
error_message IS NULL);

 

[lewisp]

The SELECT in the UPDATE must be returning more than one row for one of the rows you are trying to update.

 

[bond20]

Thanks for that, but I have already tried that and I get the same error.

Regards

 

[bond20]

Sorry about the earlier post. So how can I resolve this?
I thought the DISTINCT would solve it.

Regards

 

[BJCooperIT]

As lewisp has stated, the SELECT DISTINCT is returning more than one row. DISTINCT returns uniue combinations of data. If you have TEST_TABLE:

CODE  AMOUNT
A     100
A     100
B     300
C     400
C     500

select distinct CODE, AMOUNT
from   TEST_TABLE;

The result will be:

CODE  AMOUNT
A     100
B     300
C     400
C     500

The DISTINCT filters out the duplicates. Did you, by chnace, want to SUM it?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000