Menu
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 
225
Show activity on this post. 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.
Multiple Row Sub Query Multiple-row subqueries are used most commonly in WHERE and HAVING clauses. Since it returns multiple rows, it must be handled by set comparison operators (IN, ALL, ANY).
The most easiest and common way is to use join clause in the update statement and use multiple tables in the update statement. Here we can see that using join clause in update statement. We have merged two tables by the use of join clause.
Answer: C. An UPDATE can update multiple rows in one or more rows at a time based on the WHERE clause conditions. 20.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
