Why am I getting an equijoin error when using INNER JOIN in redshift?

Database is Redshift

I see this error:

[XX000] ERROR: Target table must be part of an equijoin predicate Where: SQL statement "update jwan.photos_audit_v4 set expiry_date = s.user_updated from jwan.photos_audit_v4 as t INNER JOIN _sc95897_tmp_photos_audit_v4 as s on t.user_id = s.user_id and t.position = s.position WHERE t.expiry_date is null AND s.idx = 1" PL/pgSQL function "sp_photos_v4_audit" line 272 at SQL statement

This is my code:

    -- expire previous photos that have been replaced
    update  jwan.photos_audit_v4
    set     expiry_date = s.user_updated
    from    jwan.photos_audit_v4 as t
            INNER JOIN _sc95897_tmp_photos_audit_v4 as s
            on t.user_id = s.user_id and t.position = s.position
    WHERE t.expiry_date is null AND s.idx = 1
    ;

I don't even conceptually understand what is happening. I'm not using an outerjoin so why is this happening?

From hevodata:

> Table Update Using the Outer Joins in the FROM Clause
While performing an outer join to the target table with a FROM clause and an UPDATE statement, you will witness an error:

> 5)

From AWS:

> Updates with outer joins in the FROM clause
The previous example showed an inner join specified in the FROM clause of an UPDATE statement. The following example returns an error because the FROM clause does not support outer joins to the target table:

update category set catid=100
from event left join category cat on event.catid=cat.catid
where cat.catgroup='Concerts';
ERROR:  Target table must be part of an equijoin predicate

>If the outer join is required for the UPDATE statement, you can move the outer join syntax into a subquery:

update category set catid=100
from
(select event.catid from event left join category cat on event.catid=cat.catid) eventcat
where category.catid=eventcat.catid
and catgroup='Concerts';

What is going on in my code?

The problem is that your WHERE clause doesn't include the table being updated. I know, you think it is, but you only reference the table jwan.photos_audit_v4 in the FROM clause. In general you don'r reference the target table in the FROM clause, only the tables needed to provide the data to be updated. The WHERE clause tells Redshift which rows in the target to update and you don't indicates this. So it looks like you want to update every row with all the values in the FROM clause.

This is a common error. Your update should look something like:

update  jwan.photos_audit_v4
set     expiry_date = s.user_updated
from    _sc95897_tmp_photos_audit_v4 as s
WHERE jwan.photos_audit_v4.expiry_date is null AND s.idx = 1
  AND jwan.photos_audit_v4.user_id = s.user_id 
  AND jwan.photos_audit_v4.position = s.position
;