Help insert - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Help insert

Author

Topic

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-05-30 : 13:16:01

I have two tables Customer and Products

Customer

cid email date
1 a@a.com 05/21/13
2 a@a.com 05/24/13
3 b@b.com 05/20/12
4 b@b.com 05/16/11
5 c@c.com 05/15/10
product
cid pcode
2 X
2 Y
2 Z
1 X
1 Y
3 P
4 Y
3 Z

So I want to delete the records with duplicate emails in Customer table(the one with the latest date stays). The deleted email's cid would have product codes in the product table. I want to update those records in the product table with the cid for the email that will remain in the customer table. How do i do that?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-30 : 13:41:40

Please provide data in consumable format, e.g. in the form of insert statments, so that I can try it out for you

Else, difficult to explain the whole logic in words but just to give you an idea, you would need to use the Row_Number function in order to give a sequence such that its partitioned over Cid,Email and Order by Date Desc, once you get this ..hope things would be easier for you to sort out.

Cheers
MIK

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-05-30 : 15:59:44

this is untested.

(Your tables should have unique constraint and referential constraints to prevent this problem)


--get the latest customer
select c.email, ca.cid
into #t
from   customer c
cross apply (
       select top 1 cid 
       from customer 
       where email = c.email 
       order by date desc
       ) CA
group by email
having count(*) > 1

--update product rows
update p set
       p.cid = t.cid
from   customer c
join   product p 
       on p.cid = c.cid
join   #t t
       on t.email = c.email
       and t.cid != p.cid

--delete customer rows
delete c
from   #t t
join   customer c
       on c.email = t.email
       and c.cid != t.cid

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-31 : 03:28:30


set dateformat mdy
go
declare @Customer table
(
cid int,
 email varchar(20),
 [date] datetime
 )
 insert @Customer
values(1, 'a@a.com', '05/21/13'),
(2, 'a@a.com', '05/24/13'),
(3, 'b@b.com', '05/20/12'),
(4, 'b@b.com', '05/16/11'),
(5, 'c@c.com', '05/15/10')


declare @product table
(
cid int, pcode char(1)
)
insert @product
values(2, 'X'),
(2, 'Y'),
(2, 'Z'),
(1, 'X'),
(1, 'Y'),
(3, 'P'),
(4, 'Y'),
(3, 'Z')



DECLARE @DELETED_ITEMS table
(
cid int,
MaxCID int
)
;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY email ORDER BY [date] DESC) AS Rn,*
FROM @Customer
),
CTE1
AS
(
SELECT *
FROM
(
SELECT *,MAX(CASE WHEN Rn = 1 THEN cid END) OVER (PARTITION BY email) AS MaxCID
FROM CTE
)t
WHERE Rn >1
)

UPDATE p
SET p.cid = d.MaxCID
OUTPUT DELETED.cid,INSERTED.cid INTo @DELETED_ITEMS
FROM @product p
INNER JOIN CTE1 d
On d.cid = p.cid



DELETE c
FROM @Customer c
INNER JOIN @DELETED_ITEMS d
On d.cid = c.cid


select * from @Customer
select * from @product


output
------------------------------------------

2	a@a.com	2013-05-24 00:00:00.000
3	b@b.com	2012-05-20 00:00:00.000
5	c@c.com	2010-05-15 00:00:00.000


cid	pcode
2	X
2	Y
2	Z
2	X
2	Y
3	P
3	Y
3	Z

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources