sql - 根据外键值更新每行中的值

Question

下载表:

id (primary key)
user_id
item_id
created_at
updated_at

本例中的 user_id 和 item_id 都不正确，但是，它们分别正确存储在用户和项目表中(每个表中的 import_id)。这是我要编写的脚本:

downloads.each do |download|
    user = User.find_by_import_id(download.user_id)
    item = item.find_by_import_id(download.item_id)

   if user && item
    download.update_attributes(:user_id => user.id, :item.id => item.id)
   end
end

所以，

1. 根据以下内容查找用户和项目他们各自的“import_id”。然后
2. 更新下载记录中的那些值

这需要永远处理大量的行。无论如何要在 SQL 中执行此操作？

Answer 1

如果我没理解错的话，您只需在 SELECT 语句中添加两个子查询即可查找正确的 ID。例如:

SELECT id, 
   (SELECT correct_id FROM User WHERE import_id=user_id) AS UserID,
   (SELECT correct_id FROM Item WHERE import_id=item_id) AS ItemID,
   created_at,
   updated_at
FROM Downloads

这会将您不正确的 user_ids 转换为您希望从 User 表中获得的任何 ID，并且它会对您的 item_ids 执行相同的操作。来自 SQL 的信息现在将是正确的。

但是，如果您想用正确的信息更新表格，您可以这样写:

UPDATE Downloads
SET user_id = User.user_id,
    item_id = Item.item_id
FROM Downloads
INNER JOIN User ON Downloads.user_id = User.import_id
INNER JOIN Item ON Downloads.item_id = Item.import_id
WHERE ...

确保在 WHERE 子句中添加一些内容，这样您就不会更新 Downloads 表中的每条记录(除非这是计划)。我重写了上面的语句以进行更优化，因为原始版本每行有两个 SELECT 语句，这有点紧张。

编辑:由于这是 PostgreSQL，您不能在 UPDATE 和 FROM 部分中都有表名。相反，FROM 部分中的表连接到正在更新的表。这是来自 PostgreSQL 网站的关于此的引述:

When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the fromlist, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

http://www.postgresql.org/docs/8.1/static/sql-update.html

考虑到这一点，这里有一个我认为应该可行的示例(无法测试，抱歉):

UPDATE Downloads
SET user_id = User.user_id,
    item_id = Item.item_id
FROM User, Item 
WHERE Downloads.user_id = User.import_id AND
      Downloads.item_id = Item.import_id

这是基本思想。不要忘记，您仍然需要向 WHERE 部分添加额外条件以限制更新的行。