在 Sql Server 中使用子查询更新查询 [英] Update query using Subquery in Sql Server

问题描述

我有一个简单的表格结构，如下所示:

表 tempData

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

而且我还有另一个表名 tempDataView 像这样

And I also have another table names as tempDataView like this

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

我想更新表 tempDataView ，通过根据 tempDataView - Name 比较设置标记使用 tempData - 名称

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

是的，让我向您展示我尝试过的方法，我尝试使用 Cursor 解决此问题，并且完美解决，但我正在寻找使用 Subquery

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

这是:

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.

推荐答案

即使在 UPDATE 语句中，您也可以连接两个表，

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 演示

为了更快的性能，在两个表的 marks 列上定义一个 INDEX.

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

使用SUBQUERY

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

• SQLFiddle 演示

这篇关于在 Sql Server 中使用子查询更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！