I have two tables A(i,j,k) and B(m,n).
我有两个表A(i,j,k)和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?
我想通过从表A中获取sum(j)来更新B表的'm'列。是否可以在Vertica中执行此操作?
Following code can be used for Teradata, but does Vertica have this kind of flexibility?
以下代码可用于Teradata,但Vertica是否具有这种灵活性?
Update B from (select sum(j) as m from A)a1 set m=a1.m;
2 个解决方案
#1
2
The Teradata SQL syntax won't work with Vertica, but the following query should do the same thing :
Teradata SQL语法不适用于Vertica,但以下查询应该执行相同的操作:
update B set m = (select sum(j) from A)
#2
0
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.
根据表的大小,这可能不是更新数据的有效方法。 Vertical是WORM(一次写入多次读取)存储,并未针对更新或删除进行优化。
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.
另一种方法是首先将目标表中的数据暂时移动到另一个中间(但不是临时)表。之后使用另一个表写一个连接查询以产生所需的结果,最后使用带有该连接查询的导出表。最后放下中间表。当然,这假设您已经以适合您的更新逻辑的方式对表进行了分区。
#1
2
The Teradata SQL syntax won't work with Vertica, but the following query should do the same thing :
Teradata SQL语法不适用于Vertica,但以下查询应该执行相同的操作:
update B set m = (select sum(j) from A)
#2
0
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.
根据表的大小,这可能不是更新数据的有效方法。 Vertical是WORM(一次写入多次读取)存储,并未针对更新或删除进行优化。
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.
另一种方法是首先将目标表中的数据暂时移动到另一个中间(但不是临时)表。之后使用另一个表写一个连接查询以产生所需的结果,最后使用带有该连接查询的导出表。最后放下中间表。当然,这假设您已经以适合您的更新逻辑的方式对表进行了分区。