一个表中的SQLite UPDATE字段基于另一表的外键 [英] SQLite UPDATE field in one table based on foreign key of another table

问题描述

如本问题所述(带有外键列表的SQLite字段)，我正在尝试使用python将房地产经纪人和列表保留在SQLite数据库中.

As in this question (SQLite field with a list of foreign keys), I'm trying to keep my real estate agents and listings in an SQLite DB with python.

我按照建议进行操作，现在有这样的表:

I did as suggested and now have tables that look like this:

CREATE TABLE listings(
    listing_id        INTEGER PRIMARY KEY AUTOINCREMENT,
    listing_address   TEXT UNIQUE NOT NULL,
    acq_date          DATE
    agent_id          INTEGER,
    FOREIGN KEY (agent_id) REFERENCES agent (agent_id)
)

CREATE TABLE agent(
     agent_id         INTEGER PRIMARY KEY AUTOINCREMENT,
     agent_name       TEXT NOT NULL,
     agent_last_request  TEXT
)

我现在正在尝试根据列表中的计算结果更新代理表.我的理论是，必须有一种SQL方式来使用另一个表的行中的外键更新字段.实际上，我已经有此工作了:

I am now trying to update the agent table based on calculations done from the listing. My theory is that there must be a one-line-of-sql way to update a field using the foreign key from another table's row. I already have this working, in fact:

given my_listing_id
given new_request_data

"SELECT agent_id FROM listings WHERE listing_id = ?", (my_listing_id,)
fetch etc..
"UPDATE agent SET agent_last_request = ? WHERE agent_id = ?", (new_request_data, agent_id)

但是为了正确执行此操作，在我看来应该有一行SQL使用外键更新另一张表.我尝试过类似的操作，但失败了:

But in the interest of doing this right, it seems to me that there should be one line of SQL that uses the foreign key to update another table. I tried stuff like this, but failed:

"UPDATE agent, listings SET agent_last_request = ? WHERE agent.agent_id = listings.agent_id AND listings.agent_id = ?", (new_request_data, my_listing_id)

那么当我有my_listing_id时，如何更新agent_last_request?我应该继续这样做吗?

So how do I update agent_last_request when I have my_listing_id? Should I keep doing it the long way?

推荐答案

要查找单个值，可以使用标量子查询:

To look up a single value, you can use a scalar subquery:

UPDATE agent
SET agent_last_request = ?
WHERE agent_id = (SELECT agent_id
                  FROM listings
                  WHERE listing_id = ?)

这篇关于一个表中的SQLite UPDATE字段基于另一表的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！