Re: SQL-question (two fields in one)

You can concatenate (paste) two columns (fields) together quite simply using the double vertical bar ||, as in: columnone||columntwo. You can use this combination in select statements, as in:

SELECT column1||column2 FROM table

To get a list of all instances where duplicate records exist based on two columns (which together act as primary key) try the following query (where columns 1 and 2 are the primary key, and tablename is the name of your table. X and y are two aliases of this same table).

SELECT 	x.column1, x.column2, x.column3 ...
FROM		tablename x, tablename y
WHERE	x.column1=y.column1
AND		x.column2=y.column2
AND		x.rowid <> y.rowid;

Joost Ouwerkerk
HSC Foundation
Toronto, Canada

On 12 Jun 1997 10:03:14 +0200, roar_at_link.no (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?
Received on Thu Jun 12 1997 - 00:00:00 CDT