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

Answer 2

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