CODEWITHSUNDEEP
sqldatabasesql-updatevertica
user3503711
user3503711

Reputation: 2096

Is it possible to update one table from another table without a join in Vertica?

I have two tables A(i,j,k) and B(m,n).

I want to update the 'm' column of B table by taking sum(j) from table A. Is it possible to do it in Vertica?

Following code can be used for Teradata, but does Vertica have this kind of flexibility? 

Update B from (select sum(j) as m from A)a1 set m=a1.m;

Upvotes: 0

Views: 2000

Answers (2)

victtim
victtim

Reputation: 840

Depending on the size of your tables, this may not be an efficient way to update data. Vertical is a WORM (write once read many times) store, and is not optimized for updates or deletes.

An alternate way would be to first temporarily move the data in the target table to another intermediate (but not temporary) table. After that write a join query using the other table to produce the desired result, and finally use export table with that join query. Finally drop the intermediate table. Of course, this is assuming you have partitioned your table in a way suitable for your update logic.

Upvotes: 0

Nicolas Riousset
Nicolas Riousset

Reputation: 3619

The Teradata SQL syntax won't work with Vertica, but the following query should do the same thing :

update B set m = (select sum(j) from A)

Upvotes: 2

Related Questions