Update some fields in a table

[#1]

Hello,

Having searched the documentation and thes forums for the past days, I havent found a solution yet.

I have a huge table (Table 1) containing lots of information (1 million rows) with numerous fields A (he key) ,B,C,D,E,F ....

I have a second table (Table 2) that contains only fields  A, C and D and 50000 fields

This second table contains the right value for C and D for some of the records of table 1.

So what I am trying to do is Load Table 1, and then Update fields C and D with the information stored in Table 2, without changin the information stored in fields B,E, F, ....

Example :

Table 1:

A	B	C	D	E	F	G
100	John	some text	70	Not to be changed	Not to be changed	Not to be changed
101	Marry	some text	50	Not to be changed	Not to be changed	Not to be changed
102	Bill	some text	30	Not to be changed	Not to be changed	Not to be changed
103	Sandra	some text	20	Not to be changed	Not to be changed	Not to be changed
104	Tony	some text	80	Not to be changed	Not to be changed	Not to be changed

Table 2:

A	C	D
100	New info	50
102	New info	80
103	New info	60

the result should be Table 3:

A	B	C	D	E	F	G
100	John	New info	50	Not to be changed	Not to be changed	Not to be changed
101	Marry	Some text	50	Not to be changed	Not to be changed	Not to be changed
102	Bill	New info	80	Not to be changed	Not to be changed	Not to be changed
103	Sandra	New info	60	Not to be changed	Not to be changed	Not to be changed
104	Tony	Some Text	80	Not to be changed	Not to be changed	Not to be changed

I struggled on this one but I cannot acheive any result (I was just able to fully update the lines but not partially)

Thanks in advance for any advice.

[Miguel_Angel_Baeyens]

Hello,

At a first glance, I'd make use of the ApplyMap() function, because there is a key field we can use in all tables. The script will be a three step load (first map, second map, full table) and the result will be one table with updated values. According to the info you shown above, the script looks like this

MappingTable1:
MAPPING LOAD * INLINE [
A, C
100, New info
102, New info
103, New info
];
MappingTable2:
MAPPING LOAD * INLINE [
A, D
100, 50
102, 80
103, 60
];
UpdatedTable:
LOAD A, 
     B, 
     ApplyMap('MappingTable1', A, C) AS C, // updates according to MappingTable1, if there is no value within returns original C
     ApplyMap('MappingTable2', A, D) AS D, // similar to the above with the other table
     E, 
     F, 
     G 
INLINE [
A, B, C, D, E, F, G
100, John, some text, 70, Not to be changed, Not to be changed, Not to be changed
101, Marry, some text, 50, Not to be changed, Not to be changed, Not to be changed
102, Bill, some text, 30, Not to be changed, Not to be changed, Not to be changed
103, Sandra, some text, 20, Not to be changed, Not to be changed, Not to be changed
104, Tony, some text, 80, Not to be changed, Not to be changed, Not to be changed
];

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

Thank you very much !

I am going to try this.