Comparing two tables and copy elements of table 2 into table 1

Hi,I have two tables as given below.

Table 1:

Table 2

If column1 in table 2 is equal to column 1 in table1 T & W values in table 2 has to copy in table 1, Similar strings has to follow one after the other. If the stings are unequal T & W must be empty. I want the output as below table;

[dpb]

Your new table now has three 'hi' records instead of original two but only two 'hello'.

What's the difference and what's the logic behind such that could be written?

[Haritha]

Hi,

I will edit that table

Answer 1

[Guillaume]

%create demo tables
ID = strsplit('hello new world hi hello hi')';
T = cell(6, 1); W = T;
V = [1; 2; 3; 5; 8; 7];

T1 = table(ID, T, W, V)
T2 = table(ID(4:5), {2;4}, {6;7}, [1;2], 'VariableNames', {'ID', 'T', 'W', 'V'})

%merge the two tables
[found, where] = ismember(T1.ID, T2.ID);
T1(found, {'T', 'W'}) = T2(where(found), {'T', 'W'})

[Guillaume]

My answer applies to matlab tables (which are not really the same thing as SQL tables). Your question never mentioned anything about a database so it's no wonder that it's not applicable.

In any case, you haven't told us how you're importing the data in matlab (or as what, you probably could import it as matlab table, in which case, my answer still apply).

If you are asking how to do this in SQL, then you are in the wrong forum. Go ask SQL gurus. It's been a while since I've written SQL query but I've got the feeling that it wouldn't be very hard to do what you want with a fairly simple SELECT query (maybe with an UNION).

[Haritha]

Its working for normal tables. I forget to mention the SQL. I accepted my mistake

[Ayse YILMAZ]

Hello Guillaume;

Firstly thank you for your helpful sharing. I have a question about your answer.

When I run your code in Matlab, it returns 0 as the value that is not in T2. I wonder if there is a way to return it as space or NAN or something else? What code should I write for this?

Another question is what should I do for more than two tables comparison (7 or 8 tables)?

Thank you in advance for your answer.

Answer 2

[Pruthvi G]

Use

strcmp

if the values are matching then add the values to the T and W column

[Guillaume]

ismember which uses strcmp would be a lot more useful for this.

Answer 3

[Peter Perkins]

This is just an outer join:

>> T1 = table(["hello";"new";"world";"hi";"hello";"hi"],[1;2;3;5;8;7],'VariableNames',["Word" "V"])
T1 =
  6×2 table
     Word      V
    _______    _
    "hello"    1
    "new"      2
    "world"    3
    "hi"       5
    "hello"    8
    "hi"       7
>> T2 = table(["hi";"hello"],[2;4],[6;7],'VariableNames',["Word" "T" "W"])
T2 =
  2×3 table
     Word      T    W
    _______    _    _
    "hi"       2    6
    "hello"    4    7
>> outerjoin(T1,T2,'MergeKeys',true)
ans =
  6×4 table
     Word      V     T      W 
    _______    _    ___    ___
    "hello"    1      4      7
    "hello"    8      4      7
    "hi"       5      2      6
    "hi"       7      2      6
    "new"      2    NaN    NaN
    "world"    3    NaN    NaN

[Guillaume]

Do'h! I did try using an outerjoin but using only Word as the key hence got duplicate V, T and W in the result, which I couldn't merge. Of course, If you use all columns as keys, then you get the correct outerjoin.

Note that since the data is stored in a database you don't need matlab to do the join. In SQL it'd be something like:

SELECT * FROM table1 FULL OUTERJOIN table2 ON table.Word = table2.Word