更新重复记录,保留记录(Min)(日期)相同
问题描述:
这是我的问题;更新重复记录,保留记录(Min)(日期)相同
我运行此查询,并得到那些拥有“FirstTimeBooking” = 1多行13469个用户:
Select CustUserId, COUNT(CustUserId)
FROM Bookings with (nolock)
WHERE FirstTimeBooking = 1
GROUP BY CustUserId
HAVING COUNT(CustUserId) > 1
ORDER BY COUNT(CustUserId) DESC
我需要更新所有的这些记录,对于每一行设置FirstTimeBooking = 0,除了闵( BookingDate)为每个用户(这是实际的FirstTimeBooking)。任何帮助将不胜感激。
答
我试图保持它的简单,并留在你的语法:
CREATE TABLE Bookings
(
CustUserID int
,FirstTimeBooking int
,BookingDate datetime
)
INSERT INTO Bookings VALUES
(1, 1, '20170101')
,(1, 1, '20170101') -- Duplicate on same date
,(2, 1, '20170201')
,(2, 1, '20170301') -- On a second date
,(3, 1, '20170401')
UPDATE a
SET FirstTimeBooking = 0
FROM Bookings as a
LEFT OUTER JOIN (
-- Only CustUserID 1 and 2
SELECT CustUserID, min(BookingDate) as BookingDate
FROM Bookings
WHERE FirstTimeBooking = 1
GROUP BY CustUserID
HAVING COUNT(*) > 1
) as b
on a.CustUserID = b.CustUserID
and a.BookingDate = b.BookingDate
WHERE b.BookingDate IS NULL -- only where the first booking date has not been found in subquery
-- Still got the Problem with CustUserID
SELECT *
FROM Bookings
WHERE CustUserID = 1
UPDATE b
set FirstTimeBooking = 0
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY CustUserID ORDER BY CustUserID) As Rown
FROM Bookings as b
WHERE FirstTimeBooking = 1
) as b
WHERE Rown = 2
-- This is now also solved
SELECT *
FROM Bookings
WHERE CustUserID = 1
也许你没有两个完全相同的BookingDates,但我加入这个以防万一。
Regards tgr
+0
如果您想要按店铺执行此操作,只需将商店添加到分组中B-) – tgr
对需求的一种改变;我需要更新所有记录,为每个用户设置FirstTimeBooking = 0,每个用户的PER STPT除了Min(BookingDate),他们所在的PER STORE。 即, (用户可以在多个商店中有FirstTimeBooking,但不是每个商店的多个FirstTimeBookings) – akaWizzmaster