Merge two rows in the same table

[laurenq uantrell]

I want to create a stored procedure that will merge columns from two
rows that contain duplicated contacts.

I have can easily identify the duplicates and extract the UniqueIDs as
parameters, but I can't figure out how to construct the actual update
SQL.

@KeeperID int,
@DupeID int

Update Contacts
SET
a.Info1 = LEFT(TRIM(IsNul l(a.info1,'') + ' ' IsNull(b.Info1, ''))255),
a.Info2 = LEFT(TRIM(IsNul l(a.info2,'') + ' ' IsNull(b.Info2, '')),255),
etc, etc...
FROM
(here's what I can't figure out)
Contacts a ID = @KeeperID
Contacts b ID = @DupeID

[John Bell]

Re: Merge two rows in the same table

Hi

Try:

Update A
SET
Info1 = LEFT(TRIM(IsNul l(a.info1,'') + ' ' + IsNull(b.Info1, ''))255),
Info2 = LEFT(TRIM(IsNul l(a.info2,'') + ' ' + IsNull(b.Info2, '')),255),
etc, etc...
FROM
Contacts a, Contacts b
WHERE A.ID = @KeeperID
AND B.ID = @DupeID

If you had multiple contacts you could create a table (linkingTable)
containing origin PrimaryContactI d and SecondaryContac tId, something like:

INSERT INTO LinkingTable(Pr imaryContactId, SecondaryContac tId) VALUES (
@KeeperId, @DupeID )

Update A
SET
Info1 = LEFT(TRIM(IsNul l(a.info1,'') + ' ' + IsNull(b.Info1, ''))255),
Info2 = LEFT(TRIM(IsNul l(a.info2,'') + ' ' + IsNull(b.Info2, '')),255),
etc, etc...
FROM Contacts a
JOIN linkingTable l on a.ContactId = l.PrimaryContac tId
JOIN Contacts b On l.SecondaryCont actId = b.ContactId

John


"laurenq uantrell" <laurenquantrel l@hotmail.com> wrote in message
news:1102971711 .114234.142300@ c13g2000cwb.goo glegroups.com.. .[color=blue]
>I want to create a stored procedure that will merge columns from two
> rows that contain duplicated contacts.
>
> I have can easily identify the duplicates and extract the UniqueIDs as
> parameters, but I can't figure out how to construct the actual update
> SQL.
>
> @KeeperID int,
> @DupeID int
>
> Update Contacts
> SET
> a.Info1 = LEFT(TRIM(IsNul l(a.info1,'') + ' ' IsNull(b.Info1, ''))255),
> a.Info2 = LEFT(TRIM(IsNul l(a.info2,'') + ' ' IsNull(b.Info2, '')),255),
> etc, etc...
> FROM
> (here's what I can't figure out)
> Contacts a ID = @KeeperID
> Contacts b ID = @DupeID
>[/color]

[--CELKO--]

Re: Merge two rows in the same table

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

Of course, you know better than to use the dangerous and proprietary
UPDATE.. FROM.. syntax you showed in your personal pseudo-code.