1

I have two partitioned tables. Table A is my main table and Table B is full of columns that are exact copies of some of the columns in Table A. However, there is one column in Table B that has data I need- because the matching column in Table A is full of nulls.

I would like to get rid of Table B completely, since most of it is redundant, and update the matching column in Table A with the data from the one column in Table B.

Visually,

 Table A:                Table B:
 a     b     c    d      a    b    d
 __________________     ______________
 1    null   11   A      1   joe   A
 2    null   22   B      2   bob   B
 3    null   33   C      3   sal   C

I want to fill the b column in Table A with the values from the b column in Table B, and then I no longer need Table B and can delete it. I will have to do this repeatedly since these two tables are given to me daily from two separate sources.

I cannot key these tables, since they are both partitioned.

I have tried:

 update columnb:(exec columnb from TableB) from TableA;

but I get a `length error.

Suggestions on how to approach this in any manner are appreciated.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Hopper06
  • 89
  • 2
  • 10

1 Answers1

1

To replace a column in memory you would do the following.

t1:([]a:1 2 3;b:0N)
a b
---
1
2
3

t2:([]c:`aa`bb`cc;b:5 6 7)
c  b
----
aa 5
bb 6
cc 7


t1,'t2
a b c
------
1 5 aa
2 6 bb
3 7 cc

If you are getting length errors then the columns do not have the same count and the following would solve it. The obvious problem with this solution is that it will start to repeat data if t2 has a lower column count that t1. You will have to find out why that is.

t1,'count[t1]#t2

Now for partitions, you will use the amend function to change the the b column of partitioned table, table A, at date 2007.02.23 (or whatever date your partition is). This loads the b column of tableB into memory to preform the amend. You must perform the amend for each partition.

@[`:2007.02.23/tableA/;`b;:;count[tableA]#exec b from select b from tableB where date=2007.02.23]
glenny
  • 119
  • 3
  • Thank you, friend. Though I still have to resolve the issue of the tables having different numbers of rows, you have certainly sent me along the right path. I appreciate your help very much! – Hopper06 Oct 03 '14 at 19:09