SQLite - 根据另一个表的列中的值更新列 [英] SQLite - Update a column based on values from another table's columns

问题描述

我正在尝试将 Record1 的 ID 更新为 Record2 的 ID 在以下情况下:

I am trying to update Record1's ID to Record2's ID when:

• 两个表中的名称相同，并且
• 权重在Record2中更大.

• the Name is the same in both tables, and
• Weight is greater in Record2.

记录 1

| ID | Weight | Name |
|----|--------|------|
|  1 |     10 |    a |
|  2 |     10 |    b |
|  3 |     10 |    c |

记录 2

| ID | Weight | Name |
|----|--------|------|
|  4 |     20 |    a |
|  5 |     20 |    b |
|  6 |     20 |    c |

我尝试了以下 SQLite 查询:

I have tried the following SQLite query:

update record1
set id = 
  (select record2.id 
   from record2,record1 
   where record1.name=record2.name 
   and record1.weight<record2.weight)

使用上述查询 Record1 的 ID 将所有记录更新为 4.

Using the above query Record1's ID is updated to 4 for all records.

推荐答案

Writing SELECT ...record1 引入了隐藏外部表的 record1 表的新实例.

Writing SELECT ...record1 introduces a new instance of the record1 table that hides the outer one.

为了能够在外部查询中引用当前行，只需从 FROM 子句中删除 table1:

To be able to refer to the current row in the outer query, just remove table1 from the FROM clause:

UPDATE record1
SET id = (SELECT record2.id 
          FROM record2
          WHERE record1.name   = record2.name 
            AND record1.weight < record2.weight);

这篇关于SQLite - 根据另一个表的列中的值更新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！