Update Statement Syntax

From: Simon <sdobner_at_scu.edu.au>
Date: Fri, 19 Jul 2002 12:11:47 +1000
Message-ID: <pan.2002.07.19.12.11.47.180132.1430@scu.edu.au>

G'day,

Is there a difference (functional or otherwise) between putting where clause components (which are compared to constants) in a subselect versus in the main where clause

in the following example ( and in a much more complex example I have) the resulting update is identical, but I'm not convinced that this will always be the case. How do you prove that two statements are functionally identical (or not) ?

update a
set a.col1 = 'P'
where a.col2 in (

		select b.col1
		from  b
		where b.col2 = a.col3


		)	

and a.col3 = '2'

V.

update a
set a.col1 = 'P'
where a.col2 in (

		select b.col1
		from  b
		where b.col2 = a.col3
		and a.col3 = '2'


		)	

Thanks

SD

Test tables and data follow.

create table a (

col1	varchar2(10),
col2	varchar2(10),
col3	varchar2(10) )

create table b (
col1	varchar2(10),
col2	varchar2(10) )

insert into  a values ( '','A', '1')

insert into a values ( '','A', '2')
insert into a values ( '','B', '2')

truncate table b
insert into b values ( 'A','1')
insert into b values ( 'B','2')

-- 
Simon Dobner
Oracle Database Administrator
Southern Cross University
Military Rd, Lismore,  NSW, 2480, Australia.

Received on Thu Jul 18 2002 - 21:11:47 CDT