Postgresql update column based on set of values from another table

Dummy data to illustrate my problem:

create table table1 (category_id int,unit varchar,is_valid bool);

insert into table1 (category_id, unit, is_valid)
VALUES (1, 'a', true), (2, 'z', true);

create table table2 (category_id int,unit varchar);

insert into table2 (category_id, unit)
values(1, 'a'),(1, 'b'),(1, 'c'),(2, 'd'),(2, 'e');

So the data looks like:

Table 1:

category_id	unit	is_valid
1	a	true
2	z	true

Table 2:

category_id	unit
1	a
1	b
1	c
2	d
2	e

I want to update the is_valid column in Table 1, if the category_id/unit combination from Table 1 doesn’t match any of the rows in Table 2. For example, the first row in Table 1 is valid, since (1, a) is in Table 2. However, the second row in Table 1 is not valid, since (2, z) is not in Table 2.

How can I update the column using postgresql? I tried a few different where clauses of the form UPDATE table1 SET is_valid = false WHERE... but I cannot get a WHERE clause that works how I want.

Answer

You can just set the value of is_valid the the result of a ` where exists (select …). See Demo.

update table1 t1
   set is_valid = exists (select null 
                            from table2 t2 
                           where (t2.category_id, t2.unit) = (t1.category_id, t1.unit) 
                         ); 

NOTES:

• Advantage: Query correctly sets the is_valid column regardless of the current value and is a vary simple query.
• Disadvantage: Query sets the value of is_valid for every row in the table; even thoes already correctly set.

You need to decide whether the disadvantage out ways the advantage. If so then the same basic technique in a much more complicated query:

with to_valid (category_id, unit, is_valid) as 
     (select category_id
           , unit
           , exists (select null 
                       from table2 t2 
                      where (t2.category_id, t2.unit) = (t1.category_id, t1.unit) 
                    ) 
       from table1 t1
     ) 
update table1  tu
   set is_valid = to_valid.is_valid 
  from to_valid 
 where (tu.category_id, tu.unit) = (to_valid.category_id, to_valid.unit) 
   and tu.is_valid is distinct from to_valid.is_valid;