How Do I Update a Table From Another Table Only If the Result Count is 1?
- by Russ Bradberry
I have a table of 2 tables in a one to many relationship. I want to run an update script that will update the table with the FK of the related table only if there is one result (because if there is multiple then we need to decide which one to use, in another method)
Here is what I have so far:
UPDATE import_hourly_event_reports i
SET i.banner_id = b.banner_id
FROM banner b
JOIN plan p ON b.plan_id = p.id
WHERE b.campain_id = i.campaign_id
AND b.size_id = i.size_id
AND p.site_id = i.site_id
HAVING COUNT(b.banner_id) = 1
As you can see, the HAVING clause doesn't quite work as I'd expect it. I only want to update the row in the import table with the id of the banner from the banner table if the count is equal to 1.
