Insert data from another table based on condition, if not insert specific value PSQL

Question

I have table A with an id column and a bunch of others. I also have table B which has an id column like table A and also another column called countries. However, not all id in table A are in table B. I want to insert a new column into table A called countries2 that basically inserts the countries from table B that corresponds to the ids in both tables. If a specific id from A does not exist in B, then it always puts in the VARCHAR 'none' into countries2. So for example if Table A has the ids 1, 2, 3, 4 and table B looks like:
id--country
1---'foo1'
3---'foo2'

I want table A to become something like:
id--country2--other original data from table A
1---'foo1'--...
2---'none'--...
3---'foo2'--...
4---'none'--...

It's not a confusing question, but the answer may not be extremely straight-forward... You want this done entirely in SQL? — shieldstroy
– shieldstroy, Commented Apr 10, 2014 at 0:14

Achilles · Accepted Answer · 2014-04-10 00:22:49Z

1

You need to first alter your TableA to add an extra column (i.e yourNewColumn) of type varchar/varchar2

to add the column try something like

ALTER TABLE TableA ADD COLUMN yourNewColumn varchar(10);

Then you can use something like below to update TableA

UPDATE TableA
SET yourNewColumn = ISNULL(TableB.countries, 'none')
FROM TableA
LEFT JOIN TableB ON TableA.id = TableB.id

The ISNULL function in PostgreSQL might be something like

SET yourNewColumn = CASE WHEN TableB.countries IS NULL THEN 'none' ELSE TableB.countries END

edited Apr 10, 2014 at 0:22

answered Apr 10, 2014 at 0:16

Achilles

7412 gold badges13 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert data from another table based on condition, if not insert specific value PSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related