Update field in records w/ MIN timestamp

  • 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?

  • 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.

  • 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