T-SQL Update Table From Select Flow

I was wondering how sql is executed when updating from a select statement when there is no where clause. Example: Table 1 +---------+-----------+ | Column1 | Column2 | +-------

Solution 1:

To answer your Question and this may be an interesting Answer. I was able to Test this and determined most standard Joins (Inner, Left, Full Outer) gave the Answer of the "First" row and ignored the Second row

But, a Right join did one of each:

Solution 2:

After Editing:--

WITH X AS 
(
SELECT * FROM
(
    SELECT ROW_NUMBER() over (order by Column1) As myRowNumber1,Column2 AS Column2tbl1 FROM Table1
)a1
INNER JOIN
(
    SELECT ROW_NUMBER() over (order by Column1) As myRowNumber2,Column2 AS Column2tbl2  FROM Table2
)b1
ON a1.myRowNumber1=b1.myRowNumber2
)
UPDATE X
set Column2tbl1=Column2tbl2

This can be Possible using ROWNUMBER().......

Here I Perfroming ROWNUMBER() on Both the Table then Updating the Table1 then

we can acheive...... like row 1 table 1 matches with row 1 table 2. Update to Camberwell. Then row 1 table 1 matches row 2 table 2.

Output:-

Solution 3:

I just tried this and was surprised that I didn't get an error. Instead, as you assumed Table1.Column2 was updated with the results of the first row from Table2.

   CREATE TABLE #test1 (Col1 int, Col2 varchar(10))
   CREATE TABLE #test2 (Col1 int, Col2 varchar(10))
   INSERT INTO #test1 (col1) Values (3097), (3097)
   INSERT INTO #test2 (Col1, Col2) Values (3097, 'C1'), (3097, 'C2')

    UPDATE #test1 SET Col2 = #test2.Col2 
    FROM #test1 INNER JOIN #test2 ON #test1.Col1=#test2.Col1

The results are:

    SELECT * FROM #test1

Col1    Col2
3097    C1
3097    C1