How to limit this insert

[Recordsetclown]

Hi,

I want to insert one row per each value of listingsdb_id into a table with the following fields:

field_name, field_value, listingsdbelements_id, listingsdb_id, user_id

There are an unknown and variable number of rows already in this table for each unique listingsdb_id. I've tried the following, but am at a loss as to how to limit the number of inserted rows to one per unique listingsdb_id.

INSERT
INTO default_en_listingsdbelements
(listingsdbelements_field_name,listingsdbelements_field_value,listingsdb_id,userdb_id)
SELECT 'listingagentofficephone','123-456-7890',r2.listingsdb_id,1
FROM default_en_listingsdbelements AS r1
INNER
JOIN default_en_listingsdb AS r2
ON r2.listingsdb_id = r1.listingsdb_id

r2 has one listingsdb_id per row, but this seems to insert a row into r1 for each row already in r1. Thanks for any help.

 

[r937]

put a UNIQUE constraint on listingsdb_id in the target table

then use INSERT IGNORE



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Recordsetclown]

Thanks for your reply. I may have misunderstood though, as the target table might already have 30 rows with the same listingsdb_id. I'm not understanding the unique constraint.

 

[r937]

can't find it in the documentation?

;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Recordsetclown]

Actually, no. I found lots of references to unique constraint, but none that looked like they explain your suggestion. At any rate, thanks for your reply.

 

[r937]

okay, i've had another look at your requirement...

There are an unknown and variable number of rows already in this table for each unique listingsdb_id. I've tried the following, but am at a loss as to how to limit the number of inserted rows to one per unique listingsdb_id.

if you only want one row per unique listingsdb_id, and the table already has multiple rows per unique listingsdb_id...

...then i'm not sure i understand what you're asking

do you want to remove the duplicates?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Recordsetclown]

This table is where fields defined by the user are stored. Every field is a row within the table, with column headings of field_name, field_value, listingsdbelements_id (auto-incremented), listingsdb_id, and user_id.

The application deals with real estate listings, so fields might be number of bedrooms, bathrooms, etc., all related to their respective properties by the number contained in listingsdb_id. The number of rows per listingsdb_id is variable because some listings have features that others do not. On average there are probably twenty-five rows per listing.

I mistakenly deleted the listing agent phone number field from this table, and I was looking for a way to insert a single row per unique listingsdb_id into the table. Once the row was back in place, I planned to update the phone numbers from another table.

My initial attempt appeared to insert at least one phone number row for every row already in the table, and I was looking for a way to limit that to one row per listing.

I can reload all the listings without too much trouble, so don't be concerned if this is more trouble than I anticipated. Thanks again.