October 20, 2019 at 9:34 pm
I have a query that selects the record with the MIN timestamp based on a several GROUP BY fields. The query works properly and returns 30,592 records. However, for all selected records, I want to update a Score field to a value of 1. When I try to run an update based on my select, 0 records are affected.
My SELECT query is:
SELECT cdi_type,cdi_sentemailid,cdi_contactid,cdi_leadid,cfg_linktype,MIN(cdi_time) AS cTime
FROM dbo.cdi_emaileventBase
WHERE cdi_type = 2
group by cdi_type,cdi_sentemailid,cdi_contactid,cdi_leadid,cfg_linktype
My UPDATE query that is not working is:
UPDATE dbo.cdi_emaileventBase
SET cfg_score = 1
FROM dbo.cdi_emaileventBase t1
JOIN (SELECT cdi_type,cdi_sentemailid,cdi_contactid,cdi_leadid,cfg_linktype,MIN(cdi_time) AS cTime
FROM dbo.cdi_emaileventBase
WHERE cdi_type = 2
group by cdi_type,cdi_sentemailid,cdi_contactid,cdi_leadid,cfg_linktype) t2
ON t1.cdi_type = t2.cdi_type AND t1.cdi_sentemailid = t2.cdi_sentemailid AND t1.cdi_contactid = t2.cdi_contactid AND t1.cdi_leadid = t2.cdi_leadid AND t1.cfg_linktype = t2.cfg_linktype AND t1.cdi_time = t2.cTIME
Any thoughts on why my JOIN isn't returning any records to update?
October 21, 2019 at 10:33 am
OK, I figured it out and see that you wouldn't have had enough info to answer anyway. The issue with the query was a result of a data issue. Some records have a contactid and some records have a leadid. So for the update, I needed 2 run 2 queries one for records where contactid IS NOT NULL, and one when leadid IS NOT NULL (each query including only the appropriate fields.
October 21, 2019 at 1:29 pm
I think you can do the update with a single query, something like this:
;WITH CTE AS
(
SELECT DISTINCT cdi_type,
cdi_sentemailid,
cdi_contactid,
cdi_leadid,
cfg_linktype
FROM dbo.cdi_emaileventBase
WHERE cdi_type = 2
),
CTE2 AS
(
SELECT x.*
FROM CTE z
CROSS APPLY(SELECT TOP(1) *
FROM dbo.cdi_emaileventBase x
WHERE x.cdi_type = z.cdi_type
AND x.cdi_sentemailid = z.cdi_sentemailid
AND ISNULL(x.cdi_contactid, -1) = ISNULL(z.cdi_contactid, -1)
AND ISNULL(x.cdi_leadid, -1) = ISNULL(z.cdi_leadid, -1)
AND x.cfg_linktype = z.cfg_linktype
ORDER BY x.cdi_time ASC) x
)
UPDATE CTE
SET cfg_score = 1
Or an even shorter method:
;WITH CTE AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY cdi_type,cdi_sentemailid,cdi_contactid,cdi_leadid,cfg_linktype ORDER BY cdi_time ASC) RowNum,
*
FROM dbo.cdi_emaileventBase
WHERE cdi_type = 2
)
UPDATE CTE
SET cfg_score = 1
WHERE RowNum = 1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply