Table Update with get rows

TWE

Hi,

 

I have two tables with > 50k rows and 5 -10 columns and I want to update the first table (dt1) with values from the second table (dt2). I dont want to use loops because it could take a lot of time with rows > 50k. The JSL "update" function works well but I need also the matched rows in dt2 for further use.

To show what I mean I used the "update" example from "script index" and add the name MIKE with height, weight, rank and code. I need a list with row number of matched rows. For that example it would be {1, 2, 3} because MIKE doesnt exists in dt1.

Names Default To Here( 1 );
dt1 = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt2 = New Table( "Little Class",
	Add Rows( 3 ),
	New Column( "name", Character, Nominal, Set Values( {"KATIE", "ALFRED", "HENRY", "MIKE"} ) ),
	New Column( "height", Continuous, Set Values( [999, 999, 999, 111] ) ),
	New Column( "weight", Continuous, Set Values( [999, 999, 999, 111] ) ),
	New Column( "RANK", Continuous, Set Values( [3, 1, 2, 9] ) ),
	New Column( "CODE", Continuous, Set Values( [0, 1, 1, 8] ) )
);
dt1 << Update(
	With( Data Table( "Little Class" ) ),
	Match Columns( :name = :name ),
	Add columns from Update table( {:RANK} ),
	Replace columns in Main Table( {:height} )
);

Thanks

cwillden

Here's one way that might work for you assuming name acts as a unique identifier:

Names Default To Here( 1 );
dt1 = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt2 = New Table( "Little Class",
	Add Rows( 3 ),
	New Column( "name", Character, Nominal, Set Values( {"KATIE", "ALFRED", "HENRY", "MIKE"} ) ),
	New Column( "height", Continuous, Set Values( [999, 999, 999, 111] ) ),
	New Column( "weight", Continuous, Set Values( [999, 999, 999, 111] ) ),
	New Column( "RANK", Continuous, Set Values( [3, 1, 2, 9] ) ),
	New Column( "CODE", Continuous, Set Values( [0, 1, 1, 8] ) )
);
dt1 << Update(
	With( Data Table( "Little Class" ) ),
	Match Columns( :name = :name ),
	Add columns from Update table( {:RANK} ),
	Replace columns in Main Table( {:height} )
);

//Get names as associative arrays
names1 = Associative Array(dt1:name << Get values);
names2 = Associative Array(dt2:name << Get Values);

//Get the intersection (replaces names1)
names1 << Intersect(names2);

//Get intersection names as List
intersection = names1 << Get Keys;

//Find rows
update_rows = dt2 << Get Rows Where(contains(intersection, :name)>0);