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

Question

我有一个像这样简单的表结构：

表 tempData

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

我也有另一个名为tempDataView的表格，就像这样

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

我想通过将 Marks 根据 tempDataView - Name 与 tempData - Name 进行比较来更新表格 tempDataView。

是的，让我向您展示我尝试过的方法，我尝试使用游标解决了这个问题，但我正在寻找使用子查询来解决它的方法。

就是这样：

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

其实对我来说，使用子查询解决这道题就像做作业一样。

- Narendra Pal

Answer 1

您可以在UPDATE语句中加入两个表格。

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

• SQLFiddle演示

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

使用SUBQUERY

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

• SQLFiddle演示

- John Woo

---

它是正确的。但请建议我使用子查询来完成这个任务的任何方法。 - Narendra Pal

更新了答案，使用了子查询，但我更喜欢使用连接（JOIN）而不是子查询。 - John Woo

为什么应该在“marks”列上定义一个“INDEX”？难道不应该在“Name”列上吗？ - lindelof

出现了一个错误：子查询返回多个值。当子查询跟随=，！=，<，<=，>，>=时，不允许出现这种情况，或者当子查询用作表达式时也是如此。 - Pradip

尝试单独使用子查询并进行调整，直到只获取1个结果。可能需要将 SELECT 更改为 SELECT TOP 1。 - vahanpwns

Answer 2

因为你正在学习，我建议你练习将SELECT连接转换为UPDATE或DELETE连接。首先，我建议你生成一个SELECT语句，连接这两个表：

SELECT *
FROM    tempDataView a
        INNER JOIN tempData b
            ON a.Name = b.Name

请注意，我们有两个表别名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

现在，要将该语句转换为 DELETE 语句，请使用相同的方法。下面的语句将仅从 a 中删除（保留 b 不变），对于那些按名称匹配的记录：

DELETE a
FROM    tempDataView a
        INNER JOIN tempData b
            ON a.Name = b.Name

你可以使用由JW创建的SQL Fiddle作为游乐场

- cha

---

学习的正确方式很重要，为指导学习方式而加1分。谢谢。 - Narendra Pal

Answer 3

在我的样例中，我找到了这个问题的解决方案，因为我也遇到了更新和子查询的同样问题：

UPDATE
    A
SET
    A.ValueToChange = B.NewValue
FROM
    (
        Select * From C
    ) B
Where 
    A.Id = B.Id

- sfranco

---

谢谢您的回答！为了帮助其他人阅读，您能否快速添加一下这段代码为什么解决了问题的说明？ - RedBassett

Answer 4

这个主题的标题询问如何在更新中使用子查询。以下是一个示例：

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

- Graham Laight

---

我不确定这个会怎么编译，因为没有group by语句告诉count(*)要统计什么。 - crthompson

@paqogomez 尝试一下 - 在任何有记录的表上。例如: select count(*) from EventLog where year = 2018 - Graham Laight

那么你只是在计算整个表格。我坚持我的反对票，这与问题无关（无论标题如何）。 - crthompson

这是你的权利，但是这个主题的标题是“使用子查询更新查询”，而我的示例显然正是这样做的。顺便说一下，我没有计算“整个表”——count(*)后面跟着一个“where”子句——因此它只计算符合“where”条件的行数。 - Graham Laight

Answer 5

这里有一个很好的更新操作解释和一些例子。虽然它是Postgres网站，但SQL查询对其他数据库也有效。 以下示例易于理解。

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

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

因此，对于给定的示例，最可靠的查询如下所示。

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

- Memin

---

不幸的是，第一个表单在MS SQL服务器中无法工作。 - AntoineL