Re: ORA-01427. need help with the "where exists" part

"MT" <mr_amorica_at_yahoo.com> wrote in message news:4a8ddd91.0405031729.fdd6726_at_posting.google.com...
> i've had this error before, and have found solutions on google groups
> (using where exists(select null etc.)...but this is a little different
> because of the where clause and I'm updating from the same table. if
> anyone has ideas, I'd appreciate it. thanks.
>
> I'm concatenating two columns with and adding a 0 between them, except
> when the month is = 10. example:
>
> ldg_prd_desc_yr ldg_prd_desc_mo
> --------------- ---------------
> 2004 2
> 2004 4
> 2004 8
>
> and I want to combine the two columns to get this
> 200402
> 200404
> 200408
>
>
> here is the query:
>
> update prd_stat
> set ldg_prd_desc =
> (select ldg_prd_desc_yr|| '0' || ldg_prd_desc_mo
> from prd_stat
> where ldg_prd_desc_mo <> '10')
> where exists (select null from prd_stat where ldg_prd_Desc_mo <>
> '10')
>
> this is on oracle 8.1.7
>
> thanks
> MT

MT,

The clue to your problem is in the text of the message: ORA-01427: single-row subquery returns more than one row

My suggestion would be to change your UPDATE as follows: UPDATE prd_stat

   SET ldg_prd_desc = ldg_prd_desc_yr|| '0' || ldg_prd_desc_mo    WHERE ldg_prd_Desc_mo <> '10'
;

You will note that it is much simpler because it is processing one row at a time. First, the row is selected is by the WHERE clause. Then the change is made to that row via the SET clause.

Douglas Hawthorne Received on Mon May 03 2004 - 21:57:25 CDT