0

I've got two tables, a and b, both with product_name and value. The value column in a is null.

I'd like to update the a table with values from the b table. Because of a quirk in the data, product_name is not unique in either table.

I only want to set the value when there is one unambiguous match on product_name between the two. When more than one row in a has the same product name, or more than one row matches from b, I'd like to keep the value empty. Is there an efficient way to do this in Postgres?

A simpler version of this would be to first identify unique product names in a. Then, update rows where only a single row in b matches -- but I'm also not sure how to write that constraint.

Matt Hampel
  • 5,088
  • 12
  • 52
  • 78

3 Answers3

1

You can use aggregation:

update a
   set value = b.value
   from (select b.product_name, max(b.value) as value
         from b
         group by b.product_name
         having min(b.value) = max(b.value)  -- there is only one value
        ) b
   where b.product_name = a.product_name;

Note that this assumes that b.value is not null. It is easy to include logic for null values, if that is needed.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

Just put together [How to Select Every Row Where Column Value is NOT Distinct and @Gordon Linoff's answer:

create table a (
     id serial primary key
    ,product_name text 
    ,value int
);

create table b (
     id serial primary key
    ,product_name text 
    ,value int not null
);

insert into a (product_name) values
   ('A')
   ,('B')
   ,('C')
   ,('D')
   ,('E')
   ,('E');


insert into b (product_name,value) values
    ('A',1)
   ,('A',1) 
   ,('B',42)
   ,('C',1)
   ,('C',2)
   ,('E',1)
;

update a
   set value = b.value
   from (select product_name, min(value) as value
         from b
         group by b.product_name
         having 1 = count(*)
        ) b
   where b.product_name = a.product_name
     and a.product_name not in
     (select product_name
        from a
        group by product_name
        having 1 < count(*));

@Gordon Lindof your answer fails if product_name and value both dupilcated in b (example product_name=A) and misses the requirement product_name not duplicated in a.

cske
  • 2,233
  • 4
  • 26
  • 24
1

The simple way:

update a
set value = (select min(b.value) from b where b.product_name = a.product_name)
where product_name in (select product_name from a group by product_name having count(*) = 1)
  and product_name in (select product_name from b group by product_name having count(*) = 1)
;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73