Firstly: if the subselect was returning multiple values, then the for loop will be overwriting the name on table2 multiple times for some/all records (not clean). Assuming the outcome of the for loop was intended, the original subselect could have been rewritten in some controlled way to return only 1 value for each record...
Secondly: there is no order by clause so this will occur in an unpredictable manner (i.e. simplest contrived way would be (select min(name)...)Long story short: if you can at all avoid it, never ever EVER use any kind of LOOP in a T-SQL statement.
This means that you have to select on all the fields which comprise a unique key -- a non-unique primary key is not sufficient.
Without uniqueness, you are reduced to something like @Paul Karr's loop -- and if there is not a unique correlation, then more than one target row may be updated for each source row.
To get started using a Nested Table, create a code block that declares a nested table type and instantiates a nested table of this created type (Lines 2 & 4).
This code iterates through a FOR Loop 3 times to show that the Nested Table has been initialized with 3 Null members.
The values of the Nested table are then assigned and another FOR Loop iterates 3 times to output the values of the Nested Table once again. 3 lines with no results from the null values and 3 lines with the populated data.
Nested Table Type Topics include: creating a table with this type, inserting a record with this type and updating a record with this type.
SQL courses meet the most demanding needs of the business world for advanced education in a cost-effective manner.
SQL courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.
Consequently, to prevent this, you might do this: One potential issue may arise when the subquery actually finds more than one matching row in tbl B.
If this happens, the UPDATE will terminate with an error ("subquery may return only one row").