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
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/%';
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