PostgreSQL update array with subquery data

Question

Got following SQL, category_ids is array type, trying to update its value with subquery data, looks like cc.id doesn't work here. thanks!

update product 
set category_ids = '{cc.id}'
from category cc where cc.location = 'USA';

Laurenz Albe · Accepted Answer · 2020-09-10 09:12:20Z

1

Use the array constructor:

... SET category_ids = ARRAY[cc.id]

In your query, cc.id is part of a string literal and is taken, erm, literally.

answered Sep 10, 2020 at 9:12

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Answer 1