|
-
Update query help
customerid lastname firstname emailaddress
14556928 tyler NANCY abc@hotmail.com
14556927 tyler NANCY abc@hotmail.com
I am going to create a mastercustomerid column whose value will be the min(customerid) in the above scenario.
I need a query where I can achieve this
I used the query below to achieve the above results
SELECT a.customerid, lastname, firstname, emailaddress
FROM account.Customer a Join account.Email b
ON a.customerid = b.customerid
WHERE lastname in
(
SELECT lastname
FROM account.Customer c Join account.Email d
ON c.customerid = d.customerid
GROUP BY lastname, firstname, emailaddress
HAVING count(*) >1
AND firstname = a.firstname
AND emailaddress = b.emailaddress
)
ORDER BY lastname, firstname, emailaddress
I need to update 349862 rows with a mastercustomerid . Please help me.
-
Update query help (reply)
Why is a customer in the table twice? Is it because they have multiple records in the email table or multiple records in the customer table? Or Both?
Assuming that the data below has been entered into another table (i.e. customertable), the following query will identify the mastercustomerid.
select a.customerid, a.lastname, a.firstname, a.email, b.mastercustomerid
from
customertable a join
(select lastname, firstname, email, min(customerid) as mastercustomerid from customertable group by lastname, firstname, email) as b
on a.lastname = b.lastname
and a.firstname = b.firstname
------------
Cynthia at 7/10/01 10:05:27 AM
customerid lastname firstname emailaddress
14556928 tyler NANCY abc@hotmail.com
14556927 tyler NANCY abc@hotmail.com
I am going to create a mastercustomerid column whose value will be the min(customerid) in the above scenario.
I need a query where I can achieve this
I used the query below to achieve the above results
SELECT a.customerid, lastname, firstname, emailaddress
FROM account.Customer a Join account.Email b
ON a.customerid = b.customerid
WHERE lastname in
(
SELECT lastname
FROM account.Customer c Join account.Email d
ON c.customerid = d.customerid
GROUP BY lastname, firstname, emailaddress
HAVING count(*) >1
AND firstname = a.firstname
AND emailaddress = b.emailaddress
)
ORDER BY lastname, firstname, emailaddress
I need to update 349862 rows with a mastercustomerid . Please help me.
-
Update query help (reply)
Tom,
The Customer table has 2 records because 2 different customerid 's for the same customer. Similarly same email address for those 2 customers. The idea is to identify these 2 records under one master customerid. Hope I have made myself clear
------------
Tom at 7/10/01 12:03:48 PM
Why is a customer in the table twice? Is it because they have multiple records in the email table or multiple records in the customer table? Or Both?
Assuming that the data below has been entered into another table (i.e. customertable), the following query will identify the mastercustomerid.
select a.customerid, a.lastname, a.firstname, a.email, b.mastercustomerid
from
customertable a join
(select lastname, firstname, email, min(customerid) as mastercustomerid from customertable group by lastname, firstname, email) as b
on a.lastname = b.lastname
and a.firstname = b.firstname
------------
Cynthia at 7/10/01 10:05:27 AM
customerid lastname firstname emailaddress
14556928 tyler NANCY abc@hotmail.com
14556927 tyler NANCY abc@hotmail.com
I am going to create a mastercustomerid column whose value will be the min(customerid) in the above scenario.
I need a query where I can achieve this
I used the query below to achieve the above results
SELECT a.customerid, lastname, firstname, emailaddress
FROM account.Customer a Join account.Email b
ON a.customerid = b.customerid
WHERE lastname in
(
SELECT lastname
FROM account.Customer c Join account.Email d
ON c.customerid = d.customerid
GROUP BY lastname, firstname, emailaddress
HAVING count(*) >1
AND firstname = a.firstname
AND emailaddress = b.emailaddress
)
ORDER BY lastname, firstname, emailaddress
I need to update 349862 rows with a mastercustomerid . Please help me.
-
Update query help (reply)
OK,
This query will determine the mastercustomerid for you.
select mastercustomerid, C.customerid, C.firstname, C.lastname, D.emailaddress
from Customer C join Email D
on C.customerid = D.customerid
join
(
select firstname, lastname, emailaddress, min(a.customerid) as mastercustomerid
from
Customer A join Email b
on A.customerid = B.customerid
group by firstname, lastname, emailaddress
) as E
on C.firstname = E.firstname
and C.lastname = E.lastname
and D.emailaddress = E.emailaddress
------------
Cynthia at 7/10/01 2:12:39 PM
Tom,
The Customer table has 2 records because 2 different customerid 's for the same customer. Similarly same email address for those 2 customers. The idea is to identify these 2 records under one master customerid. Hope I have made myself clear
------------
Tom at 7/10/01 12:03:48 PM
Why is a customer in the table twice? Is it because they have multiple records in the email table or multiple records in the customer table? Or Both?
Assuming that the data below has been entered into another table (i.e. customertable), the following query will identify the mastercustomerid.
select a.customerid, a.lastname, a.firstname, a.email, b.mastercustomerid
from
customertable a join
(select lastname, firstname, email, min(customerid) as mastercustomerid from customertable group by lastname, firstname, email) as b
on a.lastname = b.lastname
and a.firstname = b.firstname
------------
Cynthia at 7/10/01 10:05:27 AM
customerid lastname firstname emailaddress
14556928 tyler NANCY abc@hotmail.com
14556927 tyler NANCY abc@hotmail.com
I am going to create a mastercustomerid column whose value will be the min(customerid) in the above scenario.
I need a query where I can achieve this
I used the query below to achieve the above results
SELECT a.customerid, lastname, firstname, emailaddress
FROM account.Customer a Join account.Email b
ON a.customerid = b.customerid
WHERE lastname in
(
SELECT lastname
FROM account.Customer c Join account.Email d
ON c.customerid = d.customerid
GROUP BY lastname, firstname, emailaddress
HAVING count(*) >1
AND firstname = a.firstname
AND emailaddress = b.emailaddress
)
ORDER BY lastname, firstname, emailaddress
I need to update 349862 rows with a mastercustomerid . Please help me.
-
Update query help (reply)
Thanks for the help. Is there anyway I can automate this or write a cursor which updates the mastercustomerid field which is created and is null with these values. Could you give me an idea of how to approach this.Thanks again for the help.
------------
Tom at 7/10/01 3:19:29 PM
OK,
This query will determine the mastercustomerid for you.
select mastercustomerid, C.customerid, C.firstname, C.lastname, D.emailaddress
from Customer C join Email D
on C.customerid = D.customerid
join
(
select firstname, lastname, emailaddress, min(a.customerid) as mastercustomerid
from
Customer A join Email b
on A.customerid = B.customerid
group by firstname, lastname, emailaddress
) as E
on C.firstname = E.firstname
and C.lastname = E.lastname
and D.emailaddress = E.emailaddress
------------
Cynthia at 7/10/01 2:12:39 PM
Tom,
The Customer table has 2 records because 2 different customerid 's for the same customer. Similarly same email address for those 2 customers. The idea is to identify these 2 records under one master customerid. Hope I have made myself clear
------------
Tom at 7/10/01 12:03:48 PM
Why is a customer in the table twice? Is it because they have multiple records in the email table or multiple records in the customer table? Or Both?
Assuming that the data below has been entered into another table (i.e. customertable), the following query will identify the mastercustomerid.
select a.customerid, a.lastname, a.firstname, a.email, b.mastercustomerid
from
customertable a join
(select lastname, firstname, email, min(customerid) as mastercustomerid from customertable group by lastname, firstname, email) as b
on a.lastname = b.lastname
and a.firstname = b.firstname
------------
Cynthia at 7/10/01 10:05:27 AM
customerid lastname firstname emailaddress
14556928 tyler NANCY abc@hotmail.com
14556927 tyler NANCY abc@hotmail.com
I am going to create a mastercustomerid column whose value will be the min(customerid) in the above scenario.
I need a query where I can achieve this
I used the query below to achieve the above results
SELECT a.customerid, lastname, firstname, emailaddress
FROM account.Customer a Join account.Email b
ON a.customerid = b.customerid
WHERE lastname in
(
SELECT lastname
FROM account.Customer c Join account.Email d
ON c.customerid = d.customerid
GROUP BY lastname, firstname, emailaddress
HAVING count(*) >1
AND firstname = a.firstname
AND emailaddress = b.emailaddress
)
ORDER BY lastname, firstname, emailaddress
I need to update 349862 rows with a mastercustomerid . Please help me.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|
Reply With Quote