Aster UPDATE Column Does Not Exist

Question

I am attempting to UPDATE the values of a column in one table based on the count of values in a source table. I am using Teradata Aster.

When I submit the following correlated subquery, I get an error stating the column does not exist despite verifying that it does exist.

UPDATE table2
SET column =
(
SELECT count(*)
FROM table1
WHERE table2.column = table1.column
)

I feel there is something idiosyncratic about Aster, but I'm not certain.

score 0 · Answer 1 · answered Mar 13 '19 at 07:05

You could use below query for simple column update from another table.

  UPDATE table1
  SET col2 = table2.col2
  FROM table2 
  WHERE table1.col1 = table2.col1;

and for aggregate function in update query you could use below query.

 UPDATE table1
 SET col2 = table2.col2
 FROM (select col1, count(col2) col2 from table2 group by col1 ) table2
 WHERE table1.col1 = table2.col1;

Both the queries works fine for me.

1 Answers1