SQL:创建新字段，并填充查询结果 [英] SQL: create new field, populate with query results

问题描述

我有两个表:tbl_listings，其列为:prop_id; 另一个表:tbl_bookings，其列为:prop_id，booking_date.

我想编写一个查询，以计算prop_id在tbl_bookings中出现的所有时间，然后用该查询的结果填充tbl_listings中的新列.

我的查询如下:

ALTER TABLE tbl_listings
ADD COLUMN prop_count INT

UPDATE tbl_listings
    SET prop_count =
    (SELECT COUNT(*)
    FROM tbl_bookings
    GROUP BY prop_id)

但是由于某种原因，我收到一条错误消息:子查询返回多于1行. 我该如何解决?

解决方案

该子查询为您提供了每个不同prop_id的计数.您只能将一个计数值分配给prop_count.如果您打算在与prop_ids对应的多行中更新prop_count，则需要向您的更新中添加一个关联的子查询，该查询将tbl_bookings中的prop_id与tbl_listings中的相应prop_id相关联. /p>

随着我对您的问题的思考越来越多，我想知道您是否打算将其插入空的tbl_listings表中而不是进行更新.您可以使用以下命令执行此操作:

INSERT INTO tbl_listings(prop_id,prop_count)
SELECT prop_id, COUNT(*) as prop_count
FROM tbl_bookings
GROUP BY prop_id

如果您确实要进行更新，并且假设tbl_listings表中存在每个prop_id，则可以发出以下更新:

UPDATE tbl_listings
SET prop_count=(SELECT COUNT(*) 
                FROM tbl_bookings AS TB
                WHERE TB.prop_id=TL.prop_id)
FROM tbl_listings AS TL

如果要通过插入tbl_bookings中的新prop_id及其各自的计数来更新tbl_listings，则可以执行以下操作:

INSERT INTO tbl_listings(prop_id,prop_count)
SELECT prop_id, COUNT(*) as prop_count
FROM tbl_bookings AS TB
WHERE NOT EXISTS(SELECT prop_id -- Insert only new prop_ids/counts
                 FROM tbl_listings AS TL 
                 WHERE TL.prop_id=TB.prop_id)
GROUP BY prop_id

I have two tables: tbl_listings, with the column: prop_id; and another table: tbl_bookings, with the columns: prop_id, booking_date.

I want to write a query that counts all the times prop_id appears in tbl_bookings, and then populates a new column in tbl_listings with the results of that query.

my query looks like:

ALTER TABLE tbl_listings
ADD COLUMN prop_count INT

UPDATE tbl_listings
    SET prop_count =
    (SELECT COUNT(*)
    FROM tbl_bookings
    GROUP BY prop_id)

but for some reason I get an error saying: Subquery returns more than 1 row. How do I fix this?

解决方案

That sub-query gives you a count for each distinct prop_id. You can only assign one count value to prop_count. If you intended to update the prop_count in multiple rows that correspond to prop_ids, you will need to add a correlated subquery to your update that correlates the prop_id in tbl_bookings with the corresponding prop_id in tbl_listings.

As I think about your question more, I am wondering if you meant to insert into an empty tbl_listings table rather than update. You can do that with this command:

INSERT INTO tbl_listings(prop_id,prop_count)
SELECT prop_id, COUNT(*) as prop_count
FROM tbl_bookings
GROUP BY prop_id

If you really meant to update and assuming that each prop_id is present in your tbl_listings table, you can issue the following update:

UPDATE tbl_listings
SET prop_count=(SELECT COUNT(*) 
                FROM tbl_bookings AS TB
                WHERE TB.prop_id=TL.prop_id)
FROM tbl_listings AS TL

If you want to update tbl_listings by inserting new prop_ids from tbl_bookings and their respective counts, you can do:

INSERT INTO tbl_listings(prop_id,prop_count)
SELECT prop_id, COUNT(*) as prop_count
FROM tbl_bookings AS TB
WHERE NOT EXISTS(SELECT prop_id -- Insert only new prop_ids/counts
                 FROM tbl_listings AS TL 
                 WHERE TL.prop_id=TB.prop_id)
GROUP BY prop_id

这篇关于SQL:创建新字段，并填充查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！