How Do I Update a Table From Another Table Only If the Result Count is 1?

Posted by Russ Bradberry on Stack Overflow See other posts from Stack Overflow or by Russ Bradberry
Published on 2010-05-04T20:30:24Z Indexed on 2010/05/04 21:18 UTC
Read the original article Hit count: 199

Filed under:
|
|
|

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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about postgresql