Update Table Question

Other SQL Server Topics
1

Hi,
I'm using SOQL and am doing an update to a table.
I'd like to check the date field before updating and only update the date field if the current date is older than the date I'm trying to add.

The table being updated: LastSendDate

My Query:

SELECT ac.EmailName, max(ac.EventDate) as "LastSent"
FROM Avtive_Send_Log ac
WHERE ac.EventDate IS NOT NULL
group by ac.EmailName

Somehow I'd like to add something to the effect of:

and ac.EventDate > LastSendDate.LastSent

Not sure how to do the test condition on the table being updated.
Thanks.

2

hi

hope this helps

create sample data script

DROP TABLE IF EXISTS #Active_Send_Log
DROP TABLE IF EXISTS #LastSendDate

CREATE TABLE #LastSendDate ( EmailName VARCHAR(100) , LastSent DATE )
INSERT INTO #LastSendDate SELECT 'QWE@GMAIL.COM','2022-1-15'
INSERT INTO #LastSendDate SELECT 'abc@GMAIL.COM','2022-2-19'

CREATE TABLE #Active_Send_Log ( EmailName VARCHAR(100) , EventDate DATE )
INSERT INTO #Active_Send_Log SELECT 'QWE@GMAIL.COM','2022-2-12'
INSERT INTO #Active_Send_Log SELECT 'QWE@GMAIL.COM','2022-2-14'
INSERT INTO #Active_Send_Log SELECT 'QWE@GMAIL.COM',null
INSERT INTO #Active_Send_Log SELECT 'abc@GMAIL.COM','2022-2-12'
INSERT INTO #Active_Send_Log SELECT 'abc@GMAIL.COM','2022-2-17'

; WITH cte AS ( SELECT EmailName , MAX(EventDate) AS MAX_EVENTDATE FROM #Active_Send_Log GROUP BY EmailName ) 
	UPDATE 
		A
	SET 
		A.LastSent = CASE WHEN A.LastSent < B.MAX_EVENTDATE THEN B.MAX_EVENTDATE ELSE A.LastSent END  
	FROM 
	   #LastSendDate A 
		  JOIN
	   cte B 
		  ON A.EmailName = B.EmailName

image
image766×586 24.9 KB

3

Thanks Harishgg1!

This works is SQL, but still having trouble implementing in SOQL.
I'll see how I can tweak it.