Microsoft SQL Trigger: Will multiple join records pointing to the same record fail to update?

Background:

• I have a set of products for each numbered week starting at 1.
• I have a product "library" which is week zero holding the last saved product from any time period.
• I have a trigger that fires upon update or insert which keeps the "library" item up to date from the inserted items.

Since there can be duplicate products in the same event using a "sequence" field, my join will create multiple records to update from, which target the same library record.

Some questions:

• Will these multiples fail the update command?
• Is there a better way to update the single library product?

Code:

-- PK is ID, Week #, and Sequence #
update p set p.name = i.name
from product p join inserted i on
     p.id = i.id and p.week = 0 and p.sequence = 1 

Note: "inserted" can have multiple events. ID is like a UPC, Week is an identity, and Sequence is like an identity, but starts at 1 for each week. You can have a sequence of 2 while not having a sequence of 1 because they can delete products.

Sample Data:

 ID  WeekSequence &nb开发者_StackOverflow社区sp;Name  

12345 1     3      Lego   inserted first

12345 2     2    Lego Toy inserted second

12345 2     3    Lego Toy inserted second

Result data:

 ID  WeekSequence  Name  

12345 0     1    Lego Toy Was "Lego" now is "Lego Toy"

---

According to this BOL entry:

Use caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is, if the UPDATE statement is not deterministic.

In other words, if an UPDATE statement uses a FROM clause that has multiple rows meeting the criteria to update a single row, it is unknown which row of new data will be used. In your sample data, it is unknown whether the result was updated from the name in the row with Sequence=2 or Sequence=3.

So, if it doesn't matter which row is used for the update, what you're currently doing will work just fine. If this is a problem however, you need to write your update's FROM and WHERE clauses so that only one row is returned for each item, possibly something like the following:

;with insert2 as (
  select id, week, sequence, name,
         row_number() over(partition by id order by week desc, sequence desc) as [descOrd]
  from inserted
)
update p
set p.name = i.name
from product p
  join insert2 i on p.id = i.id and p.week = 0 and p.sequence = 1
where i.descOrd=1