在 SqlServer 中使用子查询更新查询

you can join both tables even on UPDATE statements,

UPDATE  a
SET     a.marks = b.marks
FROM    tempDataView a
INNER JOIN tempData b
ON a.Name = b.Name

• SQLFiddle Demo

for faster performance, define an INDEX on column marks on both tables.

using SUBQUERY

UPDATE  tempDataView
SET     marks =
(
SELECT marks
FROM tempData b
WHERE tempDataView.Name = b.Name
)

• SQLFiddle Demo

because you are just learning I suggest you practice converting a SELECT joins to UPDATE or DELETE joins. First I suggest you generate a SELECT statement joining these two tables:

SELECT *
FROM    tempDataView a
INNER JOIN tempData b
ON a.Name = b.Name

Then note that we have two table aliases a and b. Using these aliases you can easily generate UPDATE statement to update either table a or b. For table a you have an answer provided by JW. If you want to update b, the statement will be:

UPDATE  b
SET     b.marks = a.marks
FROM    tempDataView a
INNER JOIN tempData b
ON a.Name = b.Name

Now, to convert the statement to a DELETE statement use the same approach. The statement below will delete from a only (leaving b intact) for those records that match by name:

DELETE a
FROM    tempDataView a
INNER JOIN tempData b
ON a.Name = b.Name

You can use the SQL Fiddle created by JW as a playground

The title of this thread asks how a subquery can be used in an update. Here's an example of that:

update [dbName].[dbo].[MyTable]
set MyColumn = 1
where
(
select count(*)
from [dbName].[dbo].[MyTable] mt2
where
mt2.ID > [dbName].[dbo].[MyTable].ID
and mt2.Category = [dbName].[dbo].[MyTable].Category
) > 0

Here is a nice explanation of update operation with some examples. Although it is Postgres site, but the SQL queries are valid for the other DBs, too. The following examples are intuitive to understand.

-- Update contact names in an accounts table to match the currently assigned salesmen:


UPDATE accounts SET (contact_first_name, contact_last_name) =
(SELECT first_name, last_name FROM salesmen
WHERE salesmen.id = accounts.sales_id);


-- A similar result could be accomplished with a join:


UPDATE accounts SET contact_first_name = first_name,
contact_last_name = last_name
FROM salesmen WHERE salesmen.id = accounts.sales_id;

However, the second query may give unexpected results if salesmen.id is not a unique key, whereas the first query is guaranteed to raise an error if there are multiple id matches. Also, if there is no match for a particular accounts.sales_id entry, the first query will set the corresponding name fields to NULL, whereas the second query will not update that row at all.

Hence for the given example, the most reliable query is like the following.

UPDATE tempDataView SET (marks) =
(SELECT marks FROM tempData
WHERE tempDataView.Name = tempData.Name);

Here in my sample I find out the solution of this, because I had the same problem with updates and subquerys:

UPDATE
A
SET
A.ValueToChange = B.NewValue
FROM
(
Select * From C
) B
Where
A.Id = B.Id