Redshift update innerjoin

0
votes

I want to update newState

This select Works:

select cd.referenceid,  cd.productstate , cd.date, dateadd(day,-1,cd.date) as gestern, cd2.date as cd2date, cd2.productstate, cd.statenew
from public.contractdaily  cd
left join public.contractdaily cd2 on cd2.contractid = cd.contractid and dateadd(day,-1,cd.date) = cd2.date
where cd.productstate != cd2.productstate and cd.referenceid = 201663

This doesnt work

update contractdaily  set stateNew = 1
from
(select  cd.stateNew ,cd.referenceid,  cd.productstate as cd1productstate , cd.date, dateadd(day,-1,cd.date) as gestern, 
cd2.date as cd2date, cd2.productstate as cd2productstate, cd.referenceid as cdreferenceid
from contractdaily  cd
left join contractdaily cd2 on cd2.contractid = cd.contractid and dateadd(day,-1,cd.date) = cd2.date )foo
where contractdaily.productstate !=  foo.cd2productstate  and contractdaily.referenceid = 201663;

Error: Amazon Invalid operation: Target table must be part of an equijoin predicate; [SQL State=XX000, DB Errorcode=500310]

1
sql-updateinner-joinamazon-redshift

1 Answers

0
votes

Maybe this will help:

update contractdaily
set stateNew = 1
where referenceid = 201663
and productstate not in
(
select cd2.productstate 
from contractdaily cd
left join contractdaily cd2 on cd2.contractid = cd.contractid and dateadd(day,-1,cd.date) = cd2.date
);