Update query using Subquery in Sql Server

Question

I have a simple table Structure like this:

Table tempData

╔══════════╦═══════╗
║   NAME   ║ MARKS ║
╠══════════╬═══════╣
║ Narendra ║    80 ║
║ Ravi     ║    85 ║
║ Sanjay   ║    90 ║
╚══════════╩═══════╝

And I also have another table names as tempDataView like this

╔══════════╦═══════╗
║   NAME   ║ MARKS ║
╠══════════╬═══════╣
║ Narendra ║       ║
║ Narendra ║       ║
║ Narendra ║       ║
║ Narendra ║       ║
║ Ravi     ║       ║
║ Ravi     ║       ║
║ Sanjay   ║       ║
╚══════════╩═══════╝

I want to update the table tempDataView , by setting the Marks according to the tempDataView - Name compared with tempData - Name

Yes let me show you what I tried, I tried to solve this using the Cursor and its solved perfectly, but I am finding the way to solve it using the Subquery

Here it is:

Declare @name varchar(50),@marks varchar(50)
Declare @cursorInsert CURSOR
set @cursorInsert = CURSOR FOR
Select name,marks from tempData
OPEN @cursorInsert
FETCH NEXT FROM @cursorInsert
into @name,@marks
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE tempDataView set marks = @marks where name = @name
FETCH NEXT FROM @cursorInsert
INTO @name,@marks
END
CLOSE @cursorInsert
DEALLOCATE @cursorInsert

Actually it's like the homework for me to solve it using the Subquery.

Answer 1

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