Re: oracle version problem (update with subselect)

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 5 Mar 2002 11:32:42 -0800
Message-ID: <a636gq0fbp@drn.newsguy.com>

In article <E96h8.314760$V52.3144984_at_news.chello.at>, Franz says...
>
>I have three tables in oracle and want to update one of them
>according to the content of the two others. My update
>statement works fine under Oracle V8.1.5, but fails under
>Oracle 7.3.4
>
>The statement is:
>update ip_eq ip1 set active_chk=' '
>where
>(select value1 from ip_eq_attr ipa1
> where ipa1.eq_name=ip1.eq_name
> and ipa1.attr='#BATCHSERVER')=
>(select f1 from load_klassen_raw where rownum=1)
>and typ='KLASSE';
>

8i added the ability to use a SELECT has an lvalue and rvalue in an equation (eg: (select ... ) = (select ... ))

Looks like this update is the same as:

update ip_eq ip1

   set active_chk = ' '
 where EXISTS ( select null

                  from ip_eq_attr ipa1
                 where ipa1.eq_name = ip1.eq_name
                   and ipa1.attr = '#BATCHSERVER'
                   and ipa1.value1 = (select f1 
                                        from load_klassen_raw 
                                       where rownum = 1 ) 
              )

   and typ = 'KLASSE'
/

which will run in 7.3 (double check it -- it will update every row in IP_EQ where the type is KLASSE and there is a row in IP_EQ_ATTR that has the same EQ_NAME and an attr of #BATCHSERVER and whose value1 field is the same as the f1 field in load_klassen_raw.)

>As said, no problem under Oracle8 but it fails with
>ORA-00936: missing expression at the first "(select..."
>under Oracle7.
>
>Anyone knows what could cause this problem?
>Or what Oracle8 implicitly does here and Oracle7 not?
>
>
>
>For those who want to know what I am trying to accomplish:
>I have three tables:
>
>ip_eq:
>
>eq_name active_chk typ
>-------------------------------
>name1 X KLASSE
>name2 X UNIT
>name3 KLASSE
>...
>
>ip_eq_attr:
>
>eq_name attr value1
>-------------------------------
>name1 #BATCHSERVER server1
>name1 #ATTR2 smthg
>name2 #BATCHSERVER server1
>name2 #TIME smthg
>name3 #BATCHSERVER server2
>...
>
>load_klassen_raw:
>
>f1 f2
>---------------------
>server1 name1
>server1 name3
>...
>
>(rem: f1 contains the same value for all rows)
>
>load_klassen_raw was filled through an import via sql*loader
>and the working table ip_eq should be updated according to
>the loaded data. the first step is to set all active_chk
>fields in ip_eq to ' ' like this pseudo-sql:
>
>update ip_eq set active_chk=' '
>where
>ip_eq_attr contains the same #batchserver for the equipment (eq_name)
>as in first row of load_klassen_raw
>
>so in this example, row1 should be set to ' '.
>
>I know, the structure of the tables could be better, but it is
>not possible to change that...
>
>thanks in advance,
>--franz
>
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue Mar 05 2002 - 13:32:42 CST