Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> SQL puzzle - using where (a, b, c) in select (a, b, c from...) an

SQL puzzle - using where (a, b, c) in select (a, b, c from...) an

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 18 Nov 2002 19:03:48 -0800
Message-ID: <F001.005062C2.20021118190348@fatcity.com>


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.

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: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

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 Mon Nov 18 2002 - 21:03:48 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US