Update statement: Error: Target table must be part

Question

I get this error when I try to update a column of Table 1 that is like a column of Table 2.

Target table must be part of an equijoin predicate.

update test
   set category = t1.category
from category_type t1, test t2
where t2.link ilike '%' || t1.type || '%'
and t2.link ilike '%.nike.com/%';

Category_Type Table shown below:

type       category
sandals     shoes
boots       shoes
t-shirts    apparel
-pants      apparel

Answer 1

I don't know Redshift, but in Postgres you must not repeat the target table in the FROM clause of an UPDATE statement:

update test t2
   set category = t1.category
from category_type t1  --<< do NOT repeat the target table here
where t2.link ilike '%' || t1.type || '%'
  and t2.link ilike '%.nike.com/%'; 

Answer 2

You should be able to join with a subquery like this:

update test set category = t1.category
from (
    select c.category, t.link
    from category_type c, test t
    where t.link ilike '%' || c.type || '%'
        and t.link ilike '%.nike.com/%';
) t1
where test.link = t1.link

The AWS docs have join examples further down the page. The last example shows the benefit of the subquery.

The basic form of this query is:

update target set val=t2.val
from (
    select t.id, o.val
    from target t
    join other o on o.id=t.id
) t2
where target.id = t2.id