Seriuos Update Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Seriuos Update Problem

Author

Topic

SQL_ACTUARY
Starting Member

7 Posts

Posted - 2007-02-16 : 14:14:26

Thanks in advance to anyone who can help.

I have two tables

1) H3156_MMR_Premuim

Myindex Total_MA_Payment
1 0

2) MMR_Import_Hold_TMP
Myindex Total_MA_Payment
1 5
1 6

Use the following query to update H3156_MMR_Premium with the
TMP Table

Update H3156_MMR_Premium
Set H3156_MMR_Premium.Total_MA_Payment = H3156_MMR_Premium.Total_MA_Payment + MMR_Import_Hold_TMP.Total_MA_Payment
FROM H3156_MMR_Premium
INNER jOIN [MMR_Import_Hold_TMP]
ON H3156_MMR_Premium.myindex = [MMR_Import_Hold_TMP].myindex

-------------------------------------------------------------------

HUGE ERROR

The table only updates with the first record of 5, but never adds the
second record of 6. By adding indices, I can get it to add only the second record, but ignore the first.

I have tried left, right, and outer join statements, but the update never recognizes both records.

I don't know what else to do.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 14:33:40

[code]UPDATE H3156_MMR_Premuim
SET H3156_MMR_Premuim.Total_MA_Payment = Total_MA_Payment + x.TotSum
FROM H3156_MMR_Premuim
INNER JOIN (
SELECT MyIndex,
SUM(Total_MA_Payment) AS totsum
FROM MMR_Import_Hold_TMP
GROUP BY MyIndex
) AS x ON x.MyIndex = H3156_MMR_Premuim.MyIndex[/code]

Peter Larsson
Helsingborg, Sweden

SQL_ACTUARY
Starting Member

7 Posts

Posted - 2007-02-16 : 14:52:30

Thanks, but why would the original query not have worked? I have similar queries that don't add values, but changes codes. My concern is that 2nd and 3rd record match codes changes won't process.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 15:10:51

Yuo are trying to update one table with multiple records from another, serialized.
That is not possible.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources