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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy