Turning Select into Update Stmt for Stored Proc 1

[bustercoder]

Hello, I have this stored proc which selects customers with a 60-day bal who are not on hold which works fine, but now I need to turn this same proc into an update.

I removed the groupings and inserted the update statement as you can see which is commented below, but is this the correct way to do this? Would I still need the group by for the update? All I'm trying to do is update a hold flag from 0 to 1 when customers meet the condition of being over 60-days balance due.

ALTER PROCEDURE [dbo].[not_on_hold_over_60day]
AS
BEGIN
SELECT
RM00101.CUSTNMBR,
RM00101.CUSTNAME,
RM00101.CRLMTAMT,
[Over15Amt] = RM00101.CRLMTAMT * 1.15,
[OVER 60 BALANCE] =
sum(case when RM20101.RMDTYPAL > 6 and datediff(dd, docdate, getdate()) > 60 then -( RM20101.curtrxam)
when RM20101.RMDTYPAL <= 6 and datediff(dd, docdate, getdate()) > 60 then (RM20101.curtrxam)
end),
[BALANCE] =
sum(case when RM20101.RMDTYPAL > 6 then -(RM20101.curtrxam)
when RM20101.RMDTYPAL <= 6 then (RM20101.curtrxam)
end)
FROM
RM20101 WITH (NOLOCK) INNER JOIN
RM00101 WITH (NOLOCK) ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
--UPDATE RM00101 SET hold = 1
WHERE
CRLMTTYP=2 AND HOLD = 0
GROUP BY
RM00101.CUSTNMBR,
RM00101.CUSTNAME,
RM00101.CRLMTAMT
HAVING
sum(case when RM20101.RMDTYPAL > 6 and datediff(dd, docdate, getdate()) > 60 then -( RM20101.curtrxam)
when RM20101.RMDTYPAL <= 6 and datediff(dd, docdate, getdate()) > 60 then (RM20101.curtrxam)
end) > 0 and
sum(case when RM20101.RMDTYPAL > 6 then -(RM20101.curtrxam)
when RM20101.RMDTYPAL <= 6 then (RM20101.curtrxam)
end) > 0
ORDER BY
RM00101.CUSTNMBR
END
GO

Thanks,
Buster

 

[ESquared]

UPDATE R SET hold = 1   
FROM
   RM00101 R
   INNER JOIN (
      SELECT  
       RM00101.CUSTNMBR, 
      FROM         
       RM20101 WITH (NOLOCK) INNER JOIN
           RM00101 WITH (NOLOCK) ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
      WHERE CRLMTTYP=2 AND HOLD = 0
      GROUP BY RM00101.CUSTNMBR
      HAVING 
         sum(case when RM20101.RMDTYPAL > 6 and datediff(dd, docdate, getdate()) > 60 then -( RM20101.curtrxam)
        when RM20101.RMDTYPAL <= 6 and datediff(dd, docdate, getdate()) > 60 then (RM20101.curtrxam)
  end) > 0 and
       sum(case when RM20101.RMDTYPAL > 6 then -(RM20101.curtrxam)
        when RM20101.RMDTYPAL <= 6 then (RM20101.curtrxam)
        end) > 0
      END
  ) X ON X.CUSTNMBR = R.CUSTNMBR

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[bustercoder]

Thank you eSquared! After looking at your example (which works great btw), I tried the same approach on one more select I have to convert to an update. Can you take a quick look and tell me if it looks ok?

Here is the original select and my update below:

--over 15% of credit limit not on hold
SELECT C.CUSTNMBR, C.CUSTNAME, C.CRLMTAMT, [Over15Amt] = C.CRLMTAMT * 1.15, CD.CURTRXAM
FROM RM00101 C WITH (NOLOCK) INNER JOIN
(
select CD.CUSTNMBR, [CURTRXAM] =
SUM(CASE
WHEN CD.RMDTYPAL > 6 THEN
-(CD.curtrxam) ELSE
(CD.curtrxam)
END)
from rm20101 CD WITH (NOLOCK)
where CD.curtrxam > 0
group by CD.CUSTNMBR
) AS CD
ON C.CUSTNMBR = CD.CUSTNMBR
WHERE CRLMTTYP=2 AND [CURTRXAM] > (C.CRLMTAMT * 1.15) AND hold = 0

--over 15% of credit limit not on hold
UPDATE R SET hold = 1
FROM
RM00101 R
INNER JOIN (
SELECT
RM00101.CUSTNMBR
FROM
RM20101 WITH (NOLOCK) INNER JOIN
RM00101 WITH (NOLOCK) ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
WHERE CRLMTTYP=2 AND [CURTRXAM] > (RM00101.CRLMTAMT * 1.15) AND hold = 0
GROUP BY RM00101.CUSTNMBR
HAVING
sum(case when RM20101.RMDTYPAL > 6 then -( RM20101.curtrxam) else (RM20101.curtrxam) end) > 0
) X ON X.CUSTNMBR = R.CUSTNMBR

Thanks,
Buster

 

[ESquared]

By the way, you shouldn't be performing updates when using the NOLOCK query hint. It's a really good way to, in the long run, screw up your data.

Not only can you get values that haven't been committed yet (what if someone was doing a test with purposefully incorrect data and rolling it back) you can in fact get values that never existed in the database. Read up on this one if you don't believe me.

Your update looks fine. Always check out the result set of the derived table (the whole query inside parentheses) by selecting and running that part by itself first.



[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[bustercoder]

OK, thanks! Appreciate the advice.