Update statement: Error: Target table must be part of an equijoin predicate

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

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

Solution 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

Comments

[onlinetravel]

Tried that. But I still get an error: relation "t1" does not exist. update temp set category = t1.category from ( select c.category, t.link from category_type c, temp t where t.link ilike '%' || c.type || '%' and t1.link ilike '%.nike.com/%' ) t1 where temp.link = t1.link;

[systemjack]

I'll try and come up with an example I can run on my cluster.

[systemjack]

Looks like my example has an error in the where clause of the sub-select...I can't reference t1 from there. Will fix.

[onlinetravel]

Thanks! Unable to reference t1 outside the window

[systemjack]

If you run just the inner select does it give you a list of categories and links like you'd expect?

[onlinetravel]

Yes, it does..!