By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,576 Members | 1,970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,576 IT Pros & Developers. It's quick & easy.

Merge two rows in the same table

P: n/a
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(IsNull(a.info1,'') + ' ' IsNull(b.Info1,''))255),
a.Info2 = LEFT(TRIM(IsNull(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

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hi

Try:

Update A
SET
Info1 = LEFT(TRIM(IsNull(a.info1,'') + ' ' + IsNull(b.Info1,''))255),
Info2 = LEFT(TRIM(IsNull(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 PrimaryContactId and SecondaryContactId, something like:

INSERT INTO LinkingTable(PrimaryContactId, SecondaryContactId) VALUES (
@KeeperId, @DupeID )

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

John
"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
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(IsNull(a.info1,'') + ' ' IsNull(b.Info1,''))255),
a.Info2 = LEFT(TRIM(IsNull(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

Jul 23 '05 #2

P: n/a
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.

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.