I'm trying to build a summary table and want to add all new records that didn't previously exist for a specific column. For example a customer summary table which should have one record per customer. If new customers are added new records needs to be added to the summary table. Preferrably as a bulk query and not on an individual basis.
Here's the query I'm working with:
REPLACE INTO memberhistorylast
SELECT DISTINCTROW MemberHistory.CID FROM MemberHistory
LEFT JOIN memberhistorylast
ON (MemberHistory.CID = memberhistorylast.CID)
WHERE (memberhistorylast.CID IS NULL)
If I remove the replace into line the select query shows all the records I want to insert/replace. But the replace or insert into statements generate a "not unique table/alias: memberhistorylast" error.
I've used MS Access for years and this was a no brainer, but how do I do it in mySQL?
To raise the stakes: memberhistory is like a transaction log. After this query I was going to attempt an update to update the ...last record to be a copy of the most recent history record for each member. Shortcuts? mySQL doesn't allow GROUP BY subselects so the summary table is my work around.
