Home » SQL & PL/SQL » SQL & PL/SQL » SQL equivalence
SQL equivalence [message #186860] Wed, 09 August 2006 22:40 Go to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
update tmp
set col_1 = '1'
where col_2 IN
(select col_2 from tmp1
where col_2 < 3)
;

Is the above query equivalent to the one below:

update tmp
set col_1 = '1'
where col_2 < 3
;

Also,

update tmp
set col_1 = 'X'
where col_1 NOT IN
(select col_1 from tmp1
where col_1 LIKE '%##' or col_1 LIKE '%@@')
;

Is this equivalent to the one below:

update tmp
set col_1 = 'X'
where col_1 NOT LIKE '%##' or col_1 NOT LIKE '%@@'
;

Thanks in advance....
Re: SQL equivalence [message #186862 is a reply to message #186860] Wed, 09 August 2006 23:02 Go to previous messageGo to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
I am sorry, I missed to mention that tmp1 is a copy of tmp (both have same records)
Re: SQL equivalence [message #186901 is a reply to message #186862] Thu, 10 August 2006 01:53 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I'm not wrong, those updates will do the same (in another words, you don't need the 'TMP1' table).
Re: SQL equivalence [message #186918 is a reply to message #186860] Thu, 10 August 2006 02:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The second set of updates are not the same.
update tmp
set    col_1 = 'X'
where  col_1 NOT LIKE '%##' 
or     col_1 NOT LIKE '%@@';

Will set col_1 to 'X' for all non-null COL_1 as no value of COL_1 can fail both the NOT LIKE clauses.

update tmp
set    col_1 = 'X'
where  col_1 NOT IN (select col_1 
                     from   tmp1
                     where col_1 LIKE '%##' 
                     or col_1 LIKE '%@@');

Will update all non-null COL_1 that do not end in '@@' or '##'



Re: SQL equivalence [message #186937 is a reply to message #186860] Thu, 10 August 2006 03:53 Go to previous messageGo to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
The purpose here is to avoid using temp1 table so if second set is not equivalent then I am looking for equivalent SQL which will use only one table (tmp). The first set I believe is equivalent. Any comments please. Thanks.
Re: SQL equivalence [message #186944 is a reply to message #186937] Thu, 10 August 2006 04:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Umm - if you want to check the equivalence, why can't you run them and compare the results - that's all I'm doing.

This should be the query you're looking for
update temp_upd
set col_1 = 'X'
where col_1 NOT LIKE '%##'
and col_1 NOT LIKE '%@@';
Re: SQL equivalence [message #186948 is a reply to message #186860] Thu, 10 August 2006 04:45 Go to previous message
ramesh_samane
Messages: 63
Registered: May 2006
Member
That was what I did. I ran the query and confirmed that these SQLs are equivalent, but one of my collegue who ran this on production is saying he got different results. He is claiming that table data is not changed, so thought of getting second opinion or confirmation on this.

Thanks very much all of you for your time in investigating.
Previous Topic: Calling Oracle built-in commands using JDBC
Next Topic: Parsing strings from variable amount of elements
Goto Forum:
  


Current Time: Sun Dec 04 04:40:19 CST 2016

Total time taken to generate the page: 0.10244 seconds