HI K,
You didn't say whether you have (or want) a unique constraint on these
fields,
but if you did (or do) you may consider the use of the exceptions table to
identify which rows violate these constraints.
An example below:
SQL> create table junk (id varchar2(4), company varchar2(3),
2 country varchar2(2), status number);
Table created.
SQL> insert into junk values ('5521','ABC','US',1);
1 row created.
SQL> insert into junk values ('5521','ABC','US',-1);
1 row created.
SQL> insert into junk values ('8877','DEF','UK',0);
1 row created.
SQL> insert into junk values ('8877','DEF','UK',1);
1 row created.
SQL> commit;
SQL> alter table junk add constraint junk_unq unique (id,company,cou
alter table junk add constraint junk_unq unique (id,company,country)
*
ERROR at line 1:
ORA-02299: cannot validate (SCOTT.JUNK_UNQ) - duplicate keys found
uhhhh, we knew that :-)
run the script to create the exceptions table
and enable the constraint again using the exceptions clause
SQL> @c:\oracle\ora901\rdbms\admin\utlexcpt.sql
Table created.
SQL> alter table junk add constraint junk_unq unique (id,company,country)
2 exceptions into exceptions;
alter table junk add constraint junk_unq unique (id,company,country)
*
ERROR at line 1:
ORA-02299: cannot validate (SCOTT.JUNK_UNQ) - duplicate keys found
SQL> select * from exceptions;
ROW_ID OWNER TABLE_NAME
------------------ ------------------------------
----------------------------
CONSTRAINT
------------------------------
AAAH4LAABAAAO+HAAA SCOTT JUNK
JUNK_UNQ
AAAH4LAABAAAO+HAAB SCOTT JUNK
JUNK_UNQ
AAAH4LAABAAAO+HAAC SCOTT JUNK
JUNK_UNQ
AAAH4LAABAAAO+HAAD SCOTT JUNK
JUNK_UNQ
We can now use this table to view which rows have duplicates and to
decide based
on your business rules which should be deleted.
SQL> select * from junk where rowid in
2 (select row_id from exceptions e where e.table_name='JUNK');
ID COM CO STATUS
---- --- -- ----------
5521 ABC US 1
5521 ABC US -1
8877 DEF UK 0
8877 DEF UK 1
Hope this helps,
John
[EMAIL PROTECTED] wrote:
> Hello all,
>
> I need some SQL help .. I have a table with containing duplicate
> records but because they have differents status they really are
> duplicate .. i need to find these .. here is an example of what the
> table contains :
>
> ID Company Country Status
> 5521 ABC US 1
> 5521 ABC US -1
> 8877 DEF UK 0
> 8877 DEF UK 1
>
> I want to pull the records where all the columns are the same except
> for the status column .
>
> Any help is greatly apprecieted
>
> K
>
> _________________________________________________________________
> MSN Photos is the easiest way to share and print your photos:
> http://photos.msn.com/support/worldwide.aspx
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ora NT DBA
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
