SQL: create new field, populate with query results

field, populate, sql

Solution

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

Problem

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?

Original source