Re: Better Way around the barn

From: Ed Prochak <ed.prochak_at_magicinterface.com>
Date: 6 Jul 2005 10:57:37 -0700
Message-ID: <1120672657.523653.135760@g44g2000cwa.googlegroups.com>

no correlation between the main and sub query.

consider

create table ejp (
  2 itemnumber varchar2(8),
  3 itemid number
  4 );

Table created.

SQL> select * from ejp;

ITEMNUMB ITEMID
-------- ----------

 500              2
 55               1
333               3

SQL>  update ejp set itemnumber = (SELECT ltrim(itemnumber)
  2                               from ejp where itemnumber like ' 5%')
  3         where itemnumber like ' 5%' ;
 update ejp set itemnumber = (SELECT ltrim(itemnumber)
                              *

ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

SQL> isn't this safer?
SQL> update ejp set itemnumber = (SELECT ltrim(itemnumber)

  2                              from ejp subejp
  3                              where itemnumber like ' 5%'
  4                                and
subejp.itemnumber=ejp.itemnumber)
  5     where itemnumber like ' 5%' ;

2 rows updated.

SQL> select * from ejp ;

ITEMNUMB ITEMID
-------- ----------

500               2
55                1
333               3

SQL> Received on Wed Jul 06 2005 - 12:57:37 CDT