Insert/Update records and calculating column based on Row_Number()

Hi,

I have have two tables Tbl1 has Id, CalcFld. and Tbl2 that has Id, Value, Date.  For a given sets of Tbl2.Date in Tbl2 I want to set the Tbl1CalcFld = Tbl1.CalcFld + Tbl2.Value if it is an update and if it is an insert the nTbl1.CalcFld = Tbl2.Value.

I can get one or the other to work correctly only once and then it seems to just add 1 to the Tbl1.CalcFld if the Tbl2.Id value alread exist in Tbl1.

What am I missing?

Thanks,
John
UPDATE Tbl1
SET CalcFld = (select row_Number() over(ORDER by Value DESC) as Row
					from Tbl2 where tbl2.Id = Tbl1.Id and Tbl2.Date = '4/1/09') 
from Tbl1
IF @@ROWCOUNT = 0
BEGIN
    INSERT Tbl1 (Id, CalcFld)
	select Id, row_Number() over(ORDER by Value DESC) as Row
	from Tbl2
	where Tbl2.Date = '4/1/09' 
END

Open in new window

JohnJMAAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
I would do like this, instead of testing row count at the end of update.
INSERT Tbl1 (Id, CalcFld)
SELECT DISTINCT Id, 0
FROM Tbl2
WHERE Tbl2.Date = '4/1/09' 
AND NOT EXISTS (SELECT null FROM Tbl1 WHERE Tbl1.Id = Tbl2.Id)
 
UPDATE t1
SET t1.CalcFld = t1.CalcFld + t2.[Value]
FROM Tbl1 t1
INNER JOIN (
	SELECT Id, SUM([Value]) AS [Value]
	FROM Tbl2
	WHERE Date = '4/1/09'
	GROUP BY Id 
) t2 ON t1.Id = t2.Id

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
update t1
Set CalcFld  = t1.CalcFld + t2.Value
FROM tbl1 t1
INNER JOIN tbl2 t2 on t1.id = t2.id
WHERE t2.date = '4/1/09'
IF @@ROWCOUNT = 0
      INSERT Tbl1 (Id, CalcFld)
      SELECT id, Value
     FROM tbl2 where date = '4/1/09'
0
 
JohnJMAAuthor Commented:
Hi mswvisa1,
Thanks for the help.  Your UPDATE process works great and I am able to get the Row_Count() added to the CalcFld.  
When I run the INSERT section I get the following error that has me stumped:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.
Any idea what is causing the problem?
Thanks,
  • John
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
JohnJMAAuthor Commented:
Hi aneeshattingal,
I can get your solution to work the way it is but I can't figure out how to incorporate a SELECT statement so that the value of tbl2.is actually the vlue of the Row_Count() of each row in Ttbl2.
Thanks,
John
0
 
JohnJMAAuthor Commented:
Hi mswvisa1,

Thanks for the help.  I figured out the error message,  I had a typo in my INSERT statement, which makes everything in your solution work great.  By the way your solution is easy to incorporate into other calculations and INSERT/UPDATE routines that I will need to do.

Thanks again,
John
0
 
Kevin CrossChief Technology OfficerCommented:
Glad you found the issue and it worked for you.

Best regards,
Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.