What is the best way to append data from one field to another?

[don42]

I have the following table, table1:
Name Null? Type
------------------------------- -------- ----
MAIL_ID NOT NULL NUMBER(10)
LAST_NAME VARCHAR2(45)
FIRST_NAME VARCHAR2(45)
MIDDLE_INITIAL VARCHAR2(1)
ADDRESS_1 VARCHAR2(45)
CITY VARCHAR2(35)
STATE VARCHAR2(2)
ZIP VARCHAR2(10)
REMARKS VARCHAR2(200)

The table has duplicate entries that need to be removed. The records that will be removed need the
data in the Remarks column appended to the Remarks data of the record that is not deleted.

For example, the following listing shows a sample of the duplicate records.

Mail ID Last Name First Name M Address City St ZIP Remarks
------- --------- ---------- - --------------- ----------- -- ---------- --------------------------------------------------
189 BROWN STEPHEN 6706 MOESER LN EL CERRITO CA 94530-2909 Sf7#s124,f16#d7996(NML)[Cl#117][Ml#1649][NMf1#d288][NCf9#d319][SNl#e62]

211023 BROWN STEPHEN B 6706 MOESER LN EL CERRITO CA 94530 RLl#a12047[IDl#i398]

287796 BROWN STEPHEN B 6706 MOESER LN EL CERRITO CA 94530 SNl#e1163


The following listing shows how the kept record should appear after the duplicate records are deleted.

Mail ID Last Name First Name M Address City St ZIP Remarks
------- --------- ---------- - --------------- ----------- -- ---------- --------------------------------------------------
189 BROWN STEPHEN 6706 MOESER LN EL CERRITO CA 94530-2909 Sf7#s124,f16#d7996(NML)[Cl#117][Ml#1649][NMf1#d288][NCf9#d319][SNl#e62]RLl#a12047[IDl#i398]SNl#e1163


I have the process of deleting duplicates working but have yet to determine the best way to move
the Remarks data from the deleted records to the preserved record.

Here is the sql for deleting duplicates.

DELETE FROM table1
WHERE mail_id in (SELECT mail_id FROM table1
where not first_name = 'Null' and
not last_name = 'Null' and
not city = 'Null' and
not state = 'Null'and
not last_name = 'Anon'
minus
select min(mail_id) from table1
group by first_name, last_name, city, state, address_1, organization, title);

Any suggestions will be greatly appreciated.

THANKS in advance!!!!

 

[fredericofonseca]

The following is an example with Transact SQL

DECLARE @t varchar(30)
DECLARE @List varchar(7000)
Set @List = ' '
DECLARE C1 CURSOR FOR SELECT name FROM sysobjects WHERE type = 'U'
OPEN C1
FETCH NEXT FROM C1 INTO @t
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @List= @List + ','+ @t
END
FETCH NEXT FROM C1 INTO @t
END

DEALLOCATE C1

Select @List



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

Pressed the send too soon.

For your particular case you can create a temporary table with the individual fields that select the fields that identify each master record that has duplicates.
Then loop through that table, process something similar to the code post where you select all records that match the temporary table one (excluding the exact match), do the concatenation, and update the main table.


There may be other ways, and I'm sure someone will come with a better solution.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com