I am working on a Teradata system in which I have 2 tables, Apple as A & Ball as B. Apple has 2 columns primId(Integer) and updateValue(Integer). B only has primId(integer). What I am trying to do is, when A.primId=B.primId, then set updateValue=1 else to 0.
Table Apple:Columns : Primary-key primId(Integer)Other Columns : updateValue-(Integer) Table Ball :Columns : Primary-key primId(INteger)
Queries I tried :
UPDATE a FROM apple aLEFT JOIN ball b ON a.id = b.idSET a.updatevalue = CASE WHEN b.id > 0 THEN 1 ELSE 0 END
update apple set updatevalue = (case when exists (select 1 from ball b where apple.primId = b.primId) then 1 else 0 end);
For 1st query I get from is not allowed with a join, and for second I get when clause is invalid. What am I doing wrong? Where is some teradata SQL syntax resource as well?
Try something like this
FROM apple a,
SET updatevalue = (CASE WHEN b.id > 0 THEN 1 ELSE 0 END)
where a.id = b.id