Re: SQL-question (two fields in one)

Roar Pettersen wrote:
>
> I have a problem where I need to check if I have duplicates of
> my data. The data is stored in a table, where the combination of
> two fields together make the rows unique. This makes checking
> for duplicates difficult, (or at least I have not found a solution).
> What I need to do (I think) is some way to select the two important
> fields "together" into one field in a temporary table, so that
> they make out one unique string. Does anybody know how to do
> this in sqlplus, or do I need more sophisticated tools to do
> it? (for the moment, sqlplus is all I have) And last but not
> least; HOW do I do it?
>
> (I was thinking of unloading the data to a text-file, and
> using the unix-command "sed" to paste the two fields together,
> but this seems like a problem "somebody must have had before",
> so I hope you guys can give me a better suggestion...)
> --
> Roar Pettersen
> roar_at_link.no
> http://www.link.no/roar/

Roar,

   You can concatenate two columns into a single output field if you want to but, unless I have misunderstood the question, you don't need to.

As I read it, if col1, col2 are the two columns comprising your would-be primary key, your query should be be:

select ...
from <table> T1
where (col1, col2) in
(select col1, col2
from <table> T2
group by col1, col2
having count(*) > 1);

(Make sure you have a (non-unique) index on (col1, col2) before doing this!)

Hope this helps.

Chrysalis. Received on Thu Jun 12 1997 - 00:00:00 CDT