Oracle-L: RE: SQL puzzle - using where (a, b, c) in select (a, b, c from...

From: Nirmal Kumar Muthu Kumaran <NIRMALK_at_qtel.com.qa>
Date: Mon, 18 Nov 2002 22:43:32 -0800
Message-ID: <F001.005063F3.20021118224332@fatcity.com>

SQL> ed
Wrote file afiedt.buf

  1 select * from widgets_copy
  2* order by 1,2,3
SQL> /         ID COST SELL
---------- ---------- ----------

         1         10         20
         1         10         20
         1         10         30
         1         10         30
         1         10
         1         10
         1
         1

8 rows selected.

SQL> delete widgets_copy where rowid not in (select min(rowid) 2 from widgets_copy group by id, cost,sell);

4 rows deleted.

SQL> select * from widgets_copy
2 order by 1,2,3
3 /

ID COST SELL
---------- ---------- ----------

         1         10         20
         1         10         30
         1         10
         1

SQL> HTH,
Rgds,
Nirmal.

-----Original Message-----
Sent: Tuesday, November 19, 2002 6:04 AM To: Multiple recipients of list ORACLE-L

Maybe there is a simple solution, but I'm too tired to think of one now.

I have two tables, widgets and widgets_copy. Each table has columns that can contain null values.

SQL> select id, cost, sell from widgets order by 1, 2, 3 ;

ID COST SELL
--------- --------- ---------

        1        10        20 
        1        10 
        1 

SQL> select 'ROW' || to_char (rownum) as row_num, 
  2         id, cost, sell

3 from widgets_copy
4 order by 2, 3, 4 ;

ROW_NUM ID COST SELL ---------- --------- --------- ---------

ROW3               1        10        20 
ROW6               1        10        20 
ROW7               1        10        30 
ROW8               1        10        30 
ROW2               1        10 
ROW5               1        10 
ROW1               1 
ROW4               1

SQL> I want to delete from widgets_copy all duplicates of rows that are present in widgets. Meaning I want to - delete either ROW3 or ROW6 from widgets_copy, since (1, 10, 20) is also in widgets;

delete either ROW2 or ROW5 from widgets_copy, since (1, 10, null) is also in widgets;
and delete either ROW1 or ROW4 from widgets_copy, since (1, null, null) is also in widgets.

The statement below will only delete ROW3 or ROW6 from widgets_copy. The "where (id, cost, sell) in (select id, cost, sell ..." only works when the columns do not contain nulls.

only works when none of the columns are null delete from widgets_copy where rowid in ( select min (rowid) from widgets_copy x where (x.id, x.cost, x.sell) in (select y.id, y.cost, y.sell from widgets y) group by x.id, x.cost, x.sell ) ;

How can I write my statement?
a) I don't want to use nvl because this is supposed to be a general purpose solution, and I don't know what the possible values can be in the table.

b) I could do several deletes, considering cases (a, b, c) not null, only a is null, only b is null, etc... but that would be hard to generalize to a case of a table with 4, 5 or more columns.

P.S. SQL to build the sample test case: create table widgets
  (id number, cost number, sell number) ; insert into widgets (id, cost, sell)
values (1, null, null) ;
insert into widgets (id, cost, sell)
values (1, 10, null) ;
insert into widgets (id, cost, sell)
values (1, 10, 20) ;
create table widgets_copy
as select * from widgets ;
insert into widgets_copy select * from widgets_copy ; insert into widgets_copy (id, cost, sell)   values (1, 10, 30) ;
insert into widgets_copy (id, cost, sell)   values (1, 10, 30) ;
commit ;

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Nirmal Kumar Muthu Kumaran
INET: NIRMALK_at_qtel.com.qa

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Nov 19 2002 - 00:43:32 CST