How to do a select inside an insert using Postgresql?-CodePudding

I am a beginner in postgresql and databases in general. I have a table with a column product_id. Some of the values in that column are null. I need to change those null values to the values from another table.

I want to do something like this:

insert into a(product_id) (select product_id from b where product_name='foo') where product_id = null;

I realize that this syntax doesn't work but I just need help figuring it out.

CodePudding user response：

Assuming your table name is "a" and you have some null product_id, but the othe colums does contain data.

So you need to UPDATE, not to INSERT. Your Query will be something like this :

Update a
set product_id = select product_id from b where b.product_name = 'foo' 
Where product_id is null

be sure that your sub query (select ..from b) return a unique value.

CodePudding user response：

Try below

INSERT INTO a (product_id)
select product_id from b where product_name='foo';

your where condition is wrong after the) bracket I.e. where product_id = null;