sql - 如何根据匹配的ID更新列

Question

我创建了两个表housing_listing和Buyer。如果housing_listing的id (transaction_id)与买家的id (transaction_id)匹配，如何将housing_listing表中的sold值更新为TRUE？

创建表：

CREATE TABLE housing_listing (
    transaction_id INT PRIMARY KEY,
    number_of_bedrooms INT,
    number_of_bathrooms INT,
    listing_price INT,
    listing_agent TEXT,
    agent_email TEXT,
    listing_office TEXT,
    date_of_listing DATETIME,
    zip_code INT,
    sold BOOL,
    Commission INT
);



CREATE TABLE buyer (
    transaction_id INT PRIMARY KEY,
    buyer_name TEXT,
    buyer_id INT,
    sale_price INT,
    date_of_sale DATETIME,
    selling_agent TEXT
)

将数据插入买方表：

INSERT INTO buyer VALUES (1, "Ania Kraszka", 1, 2000000,'2020/02/27','FADU');
INSERT INTO buyer VALUES (2, "Ania Kraszka", 2, 2000000,'2011/02/27','FADU');

将数据插入到housing_listing表中：

INSERT INTO housing_listing VALUES (1, 3, 2, 2000000, 'Liza','liza@uba.ar', 'UBA','2018/02/27',45049, 'FALSE',0);
INSERT INTO housing_listing VALUES (2, 2, 1, 3000, 'Tom','tom@utn.ar', 'UTN','2011/02/27',45049,'FALSE',0);
INSERT INTO housing_listing VALUES (9, 1, 1, 40000, 'Tom','tom@fadu.ar', 'FADU','2011/02/27',45049, 'FALSE',0);

Answer 1

您可以使用相关子查询：

update housing_listing
    set sold = true
    where exists (select 1
                  from buyer b
                  where b.transaction_id = housing_listing.transaction_id
                 );

我认为您的意思是交易ID匹配。