CODEWITHSUNDEEP
postgresql
Jonathan Vanasco
Jonathan Vanasco

Reputation: 15680

set TableA boolean based on TableB record

My data looks like this:

TableA
- id INT
- is_in_table_b BOOL

TableB
- id INT
- table_a_id INT

I accidentally wiped out the 'is_in_table_b' BOOL on my dev machine while reorganizing the data structures, and I forgot how I created it. It's just a shortcut for some dev benchmarks.

All the "UPATE ... FROM ...." variations I tried are setting everything as "true" based on a the join. I can't remember if I originally had a CAST in this.

Does anyone know of a simple , elegant way to accomplish this? I just want to set is_in_table_b to True if the TableA.id appears in TableB.table_a_id. I know some non-elegant ways with inner queries, but I want to remember the more-correct ways to do this. I'm positive I had this done in an "Update From" originally.

Upvotes: 0

Views: 30

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658242

Consider to drop that redundant column altogether and use a view or a "generated column" instead (with the EXISTS expression provided by @Daniel). Details under this related question:
Store common query as column?

Just be sure to have an index on TableB.table_a_id.

Upvotes: 1

erik258
erik258

Reputation: 16304

yeah, do a JOIN between the tables for an UPDATE.

the setup:

CREATE TABLE table_a ( 
  id int not null auto_increment primary key, 
  is_in_b boolean
);
CREATE TABLE table_b (
  table_a_id int
);
-- create some test data in table_a;
INSERT INTO table_a (is_in_b) VALUES (FALSE), (FALSE), (FALSE);
INSERT INTO table_a (is_in_b) SELECT FALSE 
    FROM table_a a1 
    JOIN table_a a2 
    JOIN table_a a3;
-- and create a subset of matching data in table_a;
INSERT INTO table_b (table_a_id) 
    SELECT id FROM table_a ORDER BY RAND() limit 5;

now the answer: 

UPDATE table_a 
  JOIN table_b ON table_a_id = table_a.id
SET is_in_b = TRUE;

See the results with 

SELECT * from table_b; 

SELECT * FROM table_a WHERE is_in_b;

Works on http://sqlfiddle.com/#!2/8afc0/1 - should work in Postgres too I think.

Upvotes: 1

Daniel Vérité
Daniel Vérité

Reputation: 61656

This one should be simple enough:

UPDATE tableA SET
 is_in_table_b = exists (select 1 FROM tableB WHERE table_a_id=tableA.id);

Upvotes: 2

Related Questions