Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQL help
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)
*
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)
*
SQL> select * from exceptions;
ROW_ID OWNER TABLE_NAME ------------------ ------------------------------ ----------------------------
CONSTRAINT
AAAH4LAABAAAO+HAAA SCOTT JUNKJUNK_UNQ
AAAH4LAABAAAO+HAAB SCOTT JUNKJUNK_UNQ
AAAH4LAABAAAO+HAAC SCOTT JUNKJUNK_UNQ
AAAH4LAABAAAO+HAAD SCOTT JUNKJUNK_UNQ We can now use this table to view which rows have duplicates and to decide based
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
new_oracle_user_at_hotmail.com 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: orantdba_at_netscape.net 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: 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 Apr 29 2002 - 18:54:17 CDT
![]() |
![]() |