Thanks Douglas, that worked......looks like I was trying to make it
harder than it actually is.
"Douglas Hawthorne" <douglashawthorne_at_yahoo.com.au> wrote in message news:<pEDlc.10461$TT.3124_at_news-server.bigpond.net.au>...
> "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 Tue May 04 2004 - 10:36:37 CDT
