Foreign references several primary keys in another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Foreign references several primary keys in another

Author

Topic

ohunt
Starting Member

5 Posts

Posted - 2011-10-24 : 16:36:00

i have a table (contact) with rows that have duplicate records refering to one customer and each duplicate record has a generated guid. Another table (call_req) references the different guid for the same customer. I want to update the call_req table to have the sanme guid instead of the different guids in the contact table.

Contact Table:
contact_uuid customer_identifier
123456 1254879
123457 1254879
123458 1254879

Call_req table:
contact_uuid description
123456 power issues
123457 mouse issues

If i remove duplicates using row_number function from the contact table i might be deleting the customer history from the call_req table.

How can i remove duplicates while preserving the records in the call_req table or how can i update the call_req table to have one contact_uuid for one customer?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 00:14:04

do you mean this?


UPDATE cr
SET cr.contact_uuid=c1.contact_uuid
FROM Call_req cr
JOIN Contact c
ON c.contact_uuid = cr.contact_uuid
JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY customer_identifier ORDER BY contact_uuid) AS Rn,customer_identifier,contact_uuid FROM Contact)c1
ON c1.customer_identifier = c.customer_identifier
AND c1.Rn=1
WHERE c1.contact_uuid <> cr.contact_uuid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-25 : 02:48:20

you need to keep cascade update on and update guid in contact table to have common across one customer.

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

ohunt
Starting Member

5 Posts

Posted - 2011-10-25 : 08:37:12

quote:
Originally posted by jassi.singh

you need to keep cascade update on and update guid in contact table to have common across one customer.

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Thanks for the reply.
Can the guid field be updated to be the same for all the customers? This is the primary key field and it is auto generated in the contact table.The guid was generated for every customer duplicate.
Thanks again. I appreciate all the help i can get

ohunt
Starting Member

5 Posts

Posted - 2011-10-25 : 10:49:02

quote:
Originally posted by visakh16

do you mean this?

UPDATE cr
SET cr.contact_uuid=c1.contact_uuid
FROM Call_req cr
JOIN Contact c
ON c.contact_uuid = cr.contact_uuid
JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY customer_identifier ORDER BY contact_uuid) AS Rn,customer_identifier,contact_uuid FROM Contact)c1
ON c1.customer_identifier = c.customer_identifier
AND c1.Rn=1
WHERE c1.contact_uuid <> cr.contact_uuid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ohunt
Starting Member

5 Posts

Posted - 2011-10-25 : 11:06:11

quote:
Originally posted by ohunt

quote:
Originally posted by visakh16

do you mean this?

UPDATE cr
SET cr.contact_uuid=c1.contact_uuid
FROM Call_req cr
JOIN Contact c
ON c.contact_uuid = cr.contact_uuid
JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY customer_identifier ORDER BY contact_uuid) AS Rn,customer_identifier,contact_uuid FROM Contact)c1
ON c1.customer_identifier = c.customer_identifier
AND c1.Rn=1
WHERE c1.contact_uuid <> cr.contact_uuid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank you for going over my problem.
Every guid in the call_req table matches a guid in the contact table. So the where clause (WHERE c1.contact_uuid <> cr.contact_uuid
) in the query will return matching records.

I ran a CTE using a left join on the two tables:

WITJ cte as (
SELECT ROW_NUMBER() OVER(PARTITION BY c.customer_identifier ORDER BY c.customer_identifier) AS dups,
c.customer_identifier
,c.contact_uuid
,cr.contact_uuid
from usp_contact
LEFT JOIN cr.call_req
ON c.contact_uuid = cr.contact_uuid

This code showed me the uuids from the contact table that refers to one customer identifier and also the uuids from the call_req table that has a matching record in the contact table. Because of the duplicate customer identifier having different uuids the call_req also has different uuids from the call_req which refers to on customer identifier.
I need the call_req to have one uuid from the contact table for one customer.

Thanks again for your earlier response.

ohunt
Starting Member

5 Posts

Posted - 2011-10-25 : 12:41:16

quote:
Originally posted by ohunt

quote:
Originally posted by ohunt

quote:
Originally posted by visakh16

do you mean this?

UPDATE cr
SET cr.contact_uuid=c1.contact_uuid
FROM Call_req cr
JOIN Contact c
ON c.contact_uuid = cr.contact_uuid
JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY customer_identifier ORDER BY contact_uuid) AS Rn,customer_identifier,contact_uuid FROM Contact)c1
ON c1.customer_identifier = c.customer_identifier
AND c1.Rn=1
WHERE c1.contact_uuid <> cr.contact_uuid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank you for going over my problem.
Every guid in the call_req table matches a guid in the contact table. So the where clause (WHERE c1.contact_uuid <> cr.contact_uuid
) in the query will return matching records.

I ran a CTE using a left join on the two tables:

WITJ cte as (
SELECT ROW_NUMBER() OVER(PARTITION BY c.customer_identifier ORDER BY c.customer_identifier) AS dups,
c.customer_identifier
,c.contact_uuid
,cr.contact_uuid
from usp_contact
LEFT JOIN cr.call_req
ON c.contact_uuid = cr.contact_uuid

This code showed me the uuids from the contact table that refers to one customer identifier and also the uuids from the call_req table that has a matching record in the contact table. Because of the duplicate customer identifier having different uuids the call_req also has different uuids from the call_req which refers to on customer identifier.
I need the call_req to have one uuid from the contact table for one customer.

Thanks again for your earlier response.

Disregard my reply above.
THIS CODE WORKED. BRILLIANT. AWESOME.
Thank you so much.
WOW!!!. GREAT!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-26 : 00:57:11

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources