September 1, 2017 at 1:58 pm
Hey Everyone,
I am needing help wit the Update statement below. The issue I am running into is the Max(Final Status) is not being pulled, but when I add the actual UniqueID on the where clause the update actually works.
/* Update Final Status */
Update #TestFacta
Set FinalStatus = (Select FinalStatus = CAST(SUBSTRING(MAX(CAST(b.DateNotesEntered AS BINARY(6)) + CAST(b.CallResult AS VARBINARY(1000))), 7, 1000) AS VARCHAR(1000))
From #TestFacta a
Left Join [rskgov].[dbo].[FactaTest] b on a.appnum = b.appnum and a.CreateMMYY = b.NLSCreateMMYY
where a.appnum = b.appnum and b.CallResult In ('No Phone Number In Consumer Statement',
'Verified'))
September 1, 2017 at 2:16 pm
Just got a minute, so will try to slap enough together to get you there:
Move your SELECT into a CTE then do an UPDATE/JOIN to that
with a as (
select appnum, nlscreatemmyy, bigcastthingiehere
from factatest
where b.callresult ...
group by appnum, nlscreatemmyy)
update #table
...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 1, 2017 at 2:23 pm
Thanks . I will try that
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply