Solved

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

Posted on 2009-05-05
693 Views
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
``````
0
Question by:JohnJMA
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 75

Expert Comment

ID: 24308834
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

LVL 60

Accepted Solution

Kevin Cross earned 500 total points
ID: 24308889
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
``````
0

Author Comment

ID: 24310690
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

Author Comment

ID: 24310717
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

Author Closing Comment

ID: 31578208
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

LVL 60

Expert Comment

ID: 24313680
Glad you found the issue and it worked for you.

Best regards,
Kevin
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages â€“ plus untold reputational damage to one of the worldâ€™s most trusted airlines. All due to a catastrophâ€¦
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Acâ€¦
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
###### Suggested Courses
Course of the Month8 days, 3 hours left to enroll