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

Update query using Subquery in Sql Server

提问by Narendra Pal

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 tempDataViewlike this

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

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

I want to update the table tempDataView, by setting the Marksaccording to the tempDataView- Namecompared with tempData- Name

我想更新表tempDataView，通过设置标志根据tempDataView-名称相比的TempData-名称

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

是的，让我向您展示我尝试过的内容，我尝试使用 Cursor 解决此问题，并且完美解决，但我正在找到使用子查询解决它的方法

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.

实际上，使用子查询解决它就像是我的功课。

回答by John Woo

you can join both tables even on UPDATEstatements,

您甚至可以在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 INDEXon column markson both tables.

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

using SUBQUERY

使用 SUBQUERY

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

• SQLFiddle Demo

• SQLFiddle 演示

回答by cha

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 aand 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。对于表 a，您有 JW 提供的答案。如果要更新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 aonly (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 作为游乐场

回答by Graham Laight

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

回答by Memin

Hereis 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.

这是更新操作的一个很好的解释和一些例子。虽然它是 Postgres 站点，但 SQL 查询对其他 DB 也是有效的。下面的例子直观易懂。

-- 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.

但是，如果 salesmen.id 不是唯一键，则第二个查询可能会给出意外结果，而如果有多个 id 匹配，则第一个查询肯定会引发错误。此外，如果没有匹配特定的 accounts.sales_id 条目，第一个查询会将相应的名称字段设置为 NULL，而第二个查询根本不会更新该行。

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);

回答by sfranco

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