need sql statement or storeprocedure to merge multiple registrations into one

Dear,

I need help to delete multiple registration ROWS while only keep the last ONE (the latest by contactid) and while doing this I want to accumulates total points and remaing points from multiple registrations to be deleted into the last ONE (table: dbo.customers). The main condition to execute is if email exist more than once.

For an example (at present) one email has been used to create 5 registration records:

contactid	pointstotal	pointsremaining	email   ...   ....   ...  ...
17863	85	85	theluderfamily@yahoo.com   ...   ....   ...   ...
23701	68	20	theluderfamily@yahoo.com   ...   ....   ...   ...
27415	21	21	theluderfamily@yahoo.com   ...   ....   ...   ...
30030	125	125	theluderfamily@yahoo.com   ...   ....   ...   ...
10231	387	387	theluderfamily@yahoo.com   ...   ....   ...   ...

Open in new window

Result as:
contactid	pointstotal	pointsremaining	email  ...  ... ... .. .. 
30030	686	638	theluderfamily@yahoo.com ... ...  ...  ...  ..

Open in new window

Please provide me set of sql statements to execute when email is used more than once to create registrations.

Appreciate if store procedure is provided to accomplish above job.

Thanks in advance for your assistance.


Nick

Database table:

Create Table dbo.customers(
contactid                     int                 NOT NULL,
firstname                     VARCHAR(50),
lastname                      VARCHAR(50),
address                       VARCHAR(255),
city                          VARCHAR(100),
state                         VARCHAR(100),
postcode                      VARCHAR(20),
country                       VARCHAR(100),
company                       VARCHAR(255),
phone                         VARCHAR(50),
workphone                     VARCHAR(30),
mobilephone                   VARCHAR(30),
fax                           VARCHAR(30),
email                         VARCHAR(50),
contacttypeid                 int,
comments                      VARCHAR(255),
contactreason                 VARCHAR(20),
logincount                    int,
lastlogindate                 datetime,
website                       VARCHAR(200),
password                      VARCHAR(50),
discount                      float,
custother1                    VARCHAR(100),
accountno                     VARCHAR(50),
cardno                        VARCHAR(20),
cardexpires                   VARCHAR(10),
cardname                      VARCHAR(100),
cardaddress                   VARCHAR(200),
cardcvn                       VARCHAR(50),
maillist                      bit,
productgroup                  VARCHAR(100),
cookiequestion                bit,
pointstotal                   int,
pointsremaining               int,
vatnumber                     VARCHAR(50),
userid                        VARCHAR(50),
hearaboutus                   VARCHAR(100),
address2                      VARCHAR(100),
taxexempt                     VARCHAR(50),
authorized                    bit,
cus_height                    CHAR(20),
cus_chest                     CHAR(20),
cus_waist                     CHAR(20),
cus_hat                       CHAR(20),
cus_shoe                      CHAR(20),
date_created                  datetime)
;

Open in new window

NickHowardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If by 'delete' you mean 'just show me...', then here's the query
SELECT email, SUM(pointstotal) as pointstotal_sum, sum(pointsremaining) as pointsremaining_sum, Max(Contactid) as Contactid
FROM your_table
GROUP BY email

Open in new window


If the goal is to make a SP out of it, and I'll ignor the table schema above, then here you go..
CREATE PROC name_me AS

-- Throw this all in a transaction so the entire SP succeeds or fails together

BEGIN TRY

   BEGIN TRAN tr

   -- Create a temp table to store the summarized values
   CREATE TABLE #tmp (email varchar(100), pointstotal_sum int, pointsremaining_sum int, contactid int) 

   -- Insert the summarized values into the temp table
   INSERT INTO #tmp (email, pointstotal_sum, pointsremaining_sum, contactid)
   SELECT email, SUM(pointstotal) as pointstotal_sum, sum(pointsremaining) as pointsremaining_sum, Max(Contactid) as Contactid
   FROM your_table
   GROUP BY email


   -- Delete everything from the tables
   DELETE FROM your_table

   -- Now insert the summarized values into the table
   INSERT INTO your_table (email, pointstotal_sum, pointsremaining_sum, contactid)
   SELECT email, pointstotal_sum, pointsremaining_sum, contactid
   FROM #tmp

   -- If code execution makes it here, good to go. 
   COMMIT TRAN tr

END TRY

BEGIN CATCH
   -- An error occured.  Kill the transaction 
   ROLLBACK TRAN tr
END CATCH

GO

Open in new window


Here's a couple of articles I cranked out that may help you:
SQL Server Delete Duplicate Rows Solutions
SQL Server GROUP BY Solutions
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NickHowardAuthor Commented:
Hi Jim,

Thanks for your quick answer.

I liked the SP however I think you are deleting the "customers" table and then filling from temp table. Since field "Contactid" is incremental key and it is used in "orders" table, I am afraid if it would not possible to retain it.

I wish that last "Contactid" is updated while extras are deleted. Also "orders" table is updated with correct "Contactid" since there are entries with multiple contactids while email is same.

For instance (existing):

orderid    contactid  email
19210      23701       theluderfamily@yahoo.com
20122      30030       theluderfamily@yahoo.com
19333      27415       theluderfamily@yahoo.com

Become to:

orderid    contactid  email
19210      30030      theluderfamily@yahoo.com
20122      30030       theluderfamily@yahoo.com
19333      30030       theluderfamily@yahoo.com

Again here for "orders" table update option is preferred.

Hope you got my point and I would have workable solution soon.

Many thanks.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Since field "Contactid" is incremental key and it is used in "orders" table, I am afraid if it would not possible to retain it.
>I wish that last "Contactid" is updated while extras are deleted.
>Also "orders" table is updated with correct "Contactid" since there are entries with multiple contactids while email is same.
It would greatly help if you could supply all relevant details in the initial question, as it sounds like this is now multiple questions.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

NickHowardAuthor Commented:
Jim,

Sorry I missed it. I will create new question for updating orders table.

You can for the moment resolve original request.

Thanks..
0
PortletPaulfreelancerCommented:
I was about to recommend MERGE here

BUT; I have just noticed http:#a40321979
which changes the question substantially pity it wasn't on the question itself.

The following was for the original question.
MERGE customers AS target
USING (
      SELECT
            *
      FROM (
            SELECT
                  contactid
                , email
                , ROW_NUMBER() OVER (PARTITION BY email ORDER BY contactid DESC) AS rn
                , SUM(pointstotal) OVER (PARTITION BY email)                     AS pointstotal
                , SUM(pointsremaining) OVER (PARTITION BY email)                 AS pointsremaining
            FROM customers
            ) AS s
      WHERE rn = 1
      ) AS source
            ON target.contactid = source.contactid
WHEN MATCHED
      THEN UPDATE SET
              target.pointstotal = source.pointstotal
            , target.pointsremaining = source.pointsremaining
WHEN NOT MATCHED BY SOURCE
      THEN DELETE
;
GO


select
*
from customers
GO

Open in new window

| CONTACTID | POINTSTOTAL | POINTSREMAINING |                    EMAIL |
|-----------|-------------|-----------------|--------------------------|
|     30030 |         686 |             638 | theluderfamily@yahoo.com |

Open in new window

see: http://sqlfiddle.com/#!3/e8f30/1
0
PortletPaulfreelancerCommented:
I don't understand the requirement now. Could you specify all tables involved and provide sample data for each please.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I don't understand the requirement now.
Barring any objection from Paul, I strongly suggest closing the question by splitting points, spending some time gathering all of your requirements and needed details, and asking it as a new question.
0
NickHowardAuthor Commented:
Hi Paul,

When I tried to execute the MERGE t-sql I got this error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'AS'.

Please advise.

Thanks.

t-sql
MERGE customers AS target
USING (
      SELECT
            *
      FROM (
            SELECT
                  contactid
                , email
                , ROW_NUMBER() OVER (PARTITION BY email ORDER BY contactid DESC) AS rn
                , SUM(pointstotal) OVER (PARTITION BY email)                     AS pointstotal
                , SUM(pointsremaining) OVER (PARTITION BY email)                 AS pointsremaining
            FROM customers
            ) AS s
      WHERE rn = 1
      ) AS source
            ON target.contactid = source.contactid
WHEN MATCHED
      THEN UPDATE SET
              target.pointstotal = source.pointstotal
            , target.pointsremaining = source.pointsremaining
WHEN NOT MATCHED BY SOURCE
      THEN DELETE
;
GO


select
*
from customers
GO

Open in new window

0
PortletPaulfreelancerCommented:
what version of SQL Server are you using?
(MERGE is from 2008 up)

If you are on 2005 or less then you will have to use inserts and delete separately.
0
NickHowardAuthor Commented:
I am using SQL 2005.
Nick
0
PortletPaulfreelancerCommented:
Thanks, I see you have another question open and that you specified the version (that's a very good thing to do)

Regrettably I now tend to ignore the version indicated by topic as there is only SQL Server 2005 and 2008 available as topics (sql 2012 and 2014 are available, some still cling to 2000) and so you wouldn't believe the number of times the topic does not align to the actual version. Sorry, you had it right and I took a gamble.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.