CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
sooprise
sooprise

Reputation: 23207

Updating and join on multiple rows, which row's value is used?

Let's say I have the following statement and the inner join results in 3 rows where a.Id = b.Id, but each of the 3 rows have different b.Value's. Since only one row from tableA is being updated, which of the 3 values is used in the update?

UPDATE a
SET a.Value = b.Value
FROM tableA AS a
INNER JOIN tableB as b 
ON a.Id = b.Id

Upvotes: 33

Views: 31112

Answers (6)

Tutu Kumari
Tutu Kumari

Reputation: 1

MERGE INTO table1  A
USING  table2 U
ON (lower(trim(A.src_OwnerAlias)) = lower(trim(U.ALIAS)))
WHEN MATCHED THEN
UPDATE SET  A.scout_OwnerId = U.ID
WHERE lower(trim(A.src_OwnerAlias)) = lower(trim(U.ALIAS)) AND U.SCT_COUNTRY__C ='DE' AND U.Org_name = 'EMEA2QA' ;

Upvotes: -1

E.Gannon
E.Gannon

Reputation: 11

Yes, I came up with a similar experiment to Justin Pihony:

    IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test ;
SELECT 
1 AS Name, 0 AS value 
INTO #test

IF OBJECT_ID('tempdb..#compare') IS NOT NULL DROP TABLE #compare ;
SELECT 1 AS name, 1 AS value
INTO #compare
INSERT INTO #compare
SELECT 1 AS name, 0 AS value;

SELECT * FROM #test
SELECT * FROM #compare

UPDATE t
SET t.value = c.value
FROM #test t
INNER JOIN #compare c
    ON t.Name = c.name

Takes the topmost row in the comparison, right-side table. You can reverse the #compare.value values to 0 and 1 and you'll get the reverse. I agree with the posters above...its very strange that this operation does not throw an error message as it is completely hidden that this operation IGNORES secondary values

Upvotes: 0

sudhansu63
sudhansu63

Reputation: 6200

Best option in my case for updating multiple records is to use merge Query(Supported from SQL Server 2008), in this query you have complete control of what you are updating. Also you can use output query to do further processing.

Example: Without Output clause(only update)

;WITH cteB AS
( SELECT Id, Col1, Col2, Col3  
  FROM B WHERE Id > 10  ---- Select Multiple records
)
MERGE A
USING cteB
ON(A.Id = cteB.Id) -- Update condition
WHEN MATCHED THEN UPDATE
SET  
A.Col1 = cteB.Col1,  --Note: Update condition i.e; A.Id = cteB.Id cant appear here   again.
A.Col2 = cteB.Col2,
A.Col3 = cteB.Col3;

Example: With OputPut clause

CREATE TABLE #TempOutPutTable
  {
  PkId INT NOT NULL,
  Col1 VARCHAR(50),
  Col2 VARCHAR(50)
  }

;WITH cteB AS
( SELECT Id, Col1, Col2, Col3
FROM B WHERE Id > 10
)
MERGE A
USING cteB
ON(A.Id = cteB.Id)
WHEN MATCHED THEN UPDATE
SET  
A.Col1 = cteB.Col1, 
A.Col2 = cteB.Col2,
A.Col3 = cteB.Col3
OUTPUT 
 INSERTED.Id, cteB.Col1, A.Col2 INTO #TempOutPutTable;

--Do what ever you want with the data in temporary table
SELECT * FROM #TempOutPutTable; -- you can check here which records are updated.

Upvotes: 2

JamieSee
JamieSee

Reputation: 13030

Usually what you end up with in this scenario is the first row that appears in the order of the physical index on the table. In actual practice, you should treat this as non-deterministic and include something that narrows your result to one row.

Upvotes: 9

Justin Pihony
Justin Pihony

Reputation: 67135

Here is what I came up with using SQL Server 2008

--drop table #b
--drop table #a
select 1 as id, 2 as value
into #a

select 1 as id, 5 as value
into #b

insert into #b
select 1, 3

insert into #b
select 1, 6

select * from #a
select * from #b

UPDATE #a 
SET #a.Value = #b.Value
FROM #a
INNER JOIN #b 
ON #a.Id = #b.Id

It appears that it uses the top value of a basic select each time (row 1 of select * from #b). So, it possibly depends on indexing. However, I would not rely on the implementation set by SQL, as that has the possibility of changing. Instead, I would suggest using the solution presented by Andomar to make sure you know what value you are going to choose.

In short, do not trust the default implementation, create your own. But, this was an interesting academic question :)

Upvotes: 2

Andomar
Andomar

Reputation: 238296

I don't think there are rules for this case and you cannot depend on a particular outcome.

If you're after a specific row, say the latest one, you can use apply, like:

UPDATE  a
SET     a.Value = b.Value
FROM    tableA AS a
CROSS APPLY
        (
        select  top 1 *
        from    tableB as b
        where   b.id = a.id
        order by
                DateColumn desc
        ) as b

Upvotes: 20

Related Questions