Update rows only when a join is unambiguous

Question

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.

Thorsten Kettner · Accepted Answer · 2018-10-11 19:22:51Z

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) ;

Gordon Linoff · Accepted Answer · 2018-10-11 17:12:39Z

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.

cske · Accepted Answer · 2018-10-11 19:10:28Z

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.

Collectives™ on Stack Overflow

Update rows only when a join is unambiguous

3 Answers 3

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Linked

Related