使用Sql Server中的子查询更新查询

I have a simple table Structure like this:

我有这样一个简单的表格结构:

Table tempData

表tempData

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

And I also have another table names as tempDataView like this

我还有另一个表名tempDataView

╔══════════╦═══════╗
║   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

我想更新表tempDataView，通过根据tempDataView - Name和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.

实际上，它就像我用子查询解决它的作业。

3 个解决方案

#1

101

you can join both tables even on UPDATE statements,

你可以连接两个表，即使是在UPDATE语句中，

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

SQLFiddle Demo
SQLFiddle演示

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

#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连接转换为UPDATE或DELETE连接。首先，我建议您生成一个SELECT语句连接这两个表:

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:

然后注意，我们有两个表别名a和b。使用这些别名，您可以很容易地生成UPDATE语句来更新表a或表b。如果要更新b，语句为:

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中删除(保留b不变)，用于匹配名称的记录:

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

您可以使用JW创建的SQL Fiddle

#3

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

#1

101