Update fields on n-side of 1:n-relation

I have some tables with 1:n-relations. Now I want to update the n-side of the relation. Whatever i do the result is always:
Subquery returned more than 1 value. This is not permitted ...

Both number and internalNumber are unique in table k, which is the 1-side of the relation. Ab is one of the n-side tables.

UPDATE Ab
SET knr = (select k.internalNumber from Kunden as k where kNumber=k.number)

In my opinion there should be one kNumber at a time and for each kNumber exists exactly 1 record in k. The following gives the same result:

UPDATE Ab
SET knr = k.internalNumber from Kunden as k where kNumber=k.number

Any suggestions Thanks in advance

Thomas

Answer this question

Update fields on n-side of 1:n-relation

richwu
Hi,

UPDATE Ab
SET knr = k.internalNumber
from Ab
INNER JOIN Kunden as k
ON AB.kNumber=k.number

But you should address the original problem, that you have duplictae records.

HTH, Jens SUessmeyer.

---
http://www.sqlserver2005.de
---
Yovav
The problem wasn't the posted statement but an update-trigger in table ab. This raised the error due to inconsistent test data.
adidion
The result is the same.
My tables adress a customer-sale situation. In the customer table I have a field that I added to the sales-table yesterday. This field has an 1:1 relation to k.number but this makes it easier to filter records in application. One k.number is in more than one record in ab like the customer number is in more than one sales-record.
The Tables look like this:
Kunden: Ab
number PK number PK
internalNumber knumber
knr
otherfields otherfields

knumber has already references to number in "Kunden" and I need internalNumber too in table "Ab"
Update fields on n-side of 1:n-relation

Answer this question