How Do I Update a Table From Another Table Only If the Result Count is 1?
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.开发者_如何学Pythoncampain_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.
How about
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
AND (SELECT COUNT(b1.banner_id)
FROM banner b1
JOIN plans p1 ON b1.plan_id = p1.id
WHERE b1.campain_id = i.campaign_id
AND b1.size_id = i.size_id
AND p1.site_id = i.site_id) = 1
精彩评论