Why / how does this ambiguous UPDATE statement work?

Question

Let's say you use the UPDATE statement in a table, but the information you put in this base table is taken from some other auxiliary table. Usually, you join the data and do not expect the rows in the expression of the UPDATE FROM statement to be multiplied, maintaining that one new row is matched with one old row in the base table.

But I was wondering what would happen if your JOIN table was somehow ambiguous, for example, you could not take into account each mapping of base objects to only one merged object. Or if you did something meaningless, for example, join the base table to the table of your children and update the base table using this information. How to choose it? Now in one row of the table there are several rows.

In SQL Server 2005, I ran a statement like this, and seemed to select the first row in each set. But that seems wrong to me. Isn't that a mistake? Why is this the desired behavior?

Code example

-- normal
-- categories are one-to-many bundles

update bundles_denormalized set category = c.description

from bundles_denormalized b
left join categories c
on b.category_id = c.id

-- ambiguous
-- bundles are one-to-many products

update bundles_denormalized set category = p.description

from bundles_denormalized b
left join products p
on b.id = p.bundle_id

Answer 1

From BOL to UPDATE

UPDATE FROM

UPDATE: undefined, FROM, , , UPDATE . , UPDATE script, 1 FROM UPDATE; undefined, 1 Table2.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL
    DROP TABLE dbo.Table1;
GO
IF OBJECT_ID ('dbo.Table2', 'U') IS NOT NULL
    DROP TABLE dbo.Table2;
GO
CREATE TABLE dbo.Table1 
    (ColA int NOT NULL, ColB decimal(10,3) NOT NULL);
GO
CREATE TABLE dbo.Table2 
    (ColA int PRIMARY KEY NOT NULL, ColB decimal(10,3) NOT NULL);
GO
INSERT INTO dbo.Table1 VALUES(1, 10.0);
INSERT INTO dbo.Table1 VALUES(1, 20.0);
INSERT INTO dbo.Table2 VALUES(1, 0.0);
GO
UPDATE dbo.Table2 
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
FROM dbo.Table2 
    INNER JOIN dbo.Table1 
    ON (dbo.Table2.ColA = dbo.Table1.ColA);
GO
SELECT ColA, ColB 
FROM dbo.Table2;

, , .

, FROM, .

Answer 2

, , , , , . ? , , . ? , , , , , .

:

update b    
set category = p.description
--select b.category, p.description
from bundles_denormalized b
left join products p on b.id = p.bundle_id

, , null. , , , , .

Answer 3

, - . , , , , , . , , .

, Firebird , - , . Firebird (?) sntax X X, Y ...