Prev: Scope Variables and SSIS Sequence Containers
Next: Migrating from MS Access
From: Rocky20 on 9 Jun 2010 11:16
I have two tables and I want to update one with values from the
other.

I have a membership table that holds the MemberID and Email and a
Registration table that holds a RegistrationID, Email, and a blank
field for the Memberid. What I would like to do is to match the email
addresses in both tables. When there is a match I would like to
update MemberID field in the Registration table with the MemberID from
the Membership table. However, if the email address in the
Registration table is used more than once, I do not want to use it.
So for the example data I have below my results in the registration
table would be this

980 test(a)test.com ''
981 test(a)test.com ''
982 test(a)test.com ''
983 howto(a)test.com 102
984 help(a)test.com ''
985 help(a)test.com ''
986 slow(a)test.com 104

Do I need to use a groupby clause to count the email address? Can
anyone help with this? Thanks



CREATE TABLE [dbo].[Membership](
[MemberID] [nchar](10) NOT NULL,
[Email] [nchar](50) NULL,
CONSTRAINT [PK_Membership] PRIMARY KEY CLUSTERED
(
[MemberID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Registration](
[RegistrationNumber] [nchar](10) NOT NULL,
[Email] [nchar](50) NULL,
[MemberID] [nchar](10) NULL,
CONSTRAINT [PK_Registration] PRIMARY KEY CLUSTERED
(
[RegistrationNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


insert into membership values('101','test(a)test.com')
insert into membership values('102','howto(a)test.com')
insert into membership values('103','help(a)test.com')
insert into membership values('104','slow(a)test.com')
insert into membership values('105','fast(a)test.com')

insert into registration values('980','test(a)test.com','')
insert into registration values('981','test(a)test.com','')
insert into registration values('982','test(a)test.com','')
insert into registration values('983','howto(a)test.com','')
insert into registration values('984','help(a)test.com','')
insert into registration values('985','help(a)test.com','')
insert into registration values('986','slow(a)test.com','')

From: Plamen Ratchev on 9 Jun 2010 15:04
Here is one solution:

WITH UpdateCTE AS (
SELECT R.MemberID, M.MemberID AS NewMemberId,
COUNT(*) OVER(PARTITION BY R.Email) AS cnt
FROM Registration AS R
JOIN Membership AS M
ON R.Email = M.Email)
UPDATE UpdateCTE
SET MemberID = NewMemberID
WHERE cnt = 1;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Rocky20 on 9 Jun 2010 15:11
On Jun 9, 2:04 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> Here is one solution:
>
> WITH UpdateCTE AS (
> SELECT R.MemberID, M.MemberID AS NewMemberId,
>        COUNT(*) OVER(PARTITION BY R.Email) AS cnt
> FROM Registration AS R
> JOIN Membership AS M
>   ON R.Email = M.Email)
> UPDATE UpdateCTE
> SET MemberID = NewMemberID
> WHERE cnt = 1;
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

Awesome, it works great. Thanks very much.
 | 
Pages: 1
Prev: Scope Variables and SSIS Sequence Containers
Next: Migrating from MS Access