Using Update() to add new rows into main table

[#1]

I am trying to update a table that sometimes contains new rows (based on an index).

It seems that I need to use join, as the following does not seem to work (here, the index is name column).

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( {"ALICE", "NEW_NAME", "OTHER_NEW_NAME"} )
	),
	New Column( "height",
		Continuous,
		Set Values( [999, 999, 999] )
	),
	New Column( "weight",
		Continuous,
		Set Values( [999, 999, 999] )
	),
	New Column( "RANK",
		Continuous,
		Set Values( [3, 1, 2] )
	),
	New Column( "CODE",
		Continuous,
		Set Values( [0, 1, 1] )
	)
);
dt1 << Update(
	With( Data Table( "Little Class" ) ),
	Match Columns( :name = :name ),
	Add columns from Update table( {:RANK} )
);
		

[#2]

Your stated assumption is correct, you will have to use the Join platform to do what you want.

[#3]

Found similar post from community here and it was suggested to use Join instead of update when new rows are added:

Updating JMP Data Tables 

Is there a reason why you couldn't use join instead of update (except that you will have to deal with extra tables)?

From JMP Help: Update Data Tables :

If you have two data tables and would like to update your original table with data from a new table, select Tables > Update. The Update command is a special case of Join in place. It is a Join with the Update option checked, and it does not result in a new table.

[#4]

"Except that you will have to deal with extra tables"

Exactly. A new table is generated despite I am doing the join with the update option selected.

dt<< Join(
    With( dt_new ),
    Update, // this option is selected
    By Matching Columns( :index= :index),
    Drop multiples( 0, 0 ),
    Include Nonmatches( 1, 1 ),
    Preserve main table order( 1 )
);

// Opens new table apart of dt and dt_new

[#5]

I tested the same option and it behaved same way, it did create new table.

I can share the pain with dealing of open tables in JMP, but while scripting it is fairly easy (will add some extra code thou), open/create them as invisible/private and close them immediately without saving after you know you won't be needing them anymore.

[#6]

I see. What would you recommend to keep this last step simple?

I have 'dt' (original) and the 'dt_joined' ('dt' + 'dt_new').

[#7 · Accepted solution]

Most of the time I would do it with Invisible tables, because that gives possibility to open them easily from JMPs window list (if they haven't been closed).

Invisible tables are in grey color (transparent) in the Window List and you make them visible by double clicking on them.

I also suggest adding names to tables, as it makes it easier to start debugging when you have lots of tables open and for some reason the code didn't execute properly. Without renaming they will be just became a mess like: Subset of something 1, Subset of something 2, Untitled 1 and so on.

Below is an example which will open dt as invisible and create dt_new as invisible table. Then join them to new table called JOINED_TABLE which will be shown to the user and finally close dt and dt_new without saving them.


Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp", invisible);
dt_new = New Table( "Little Class",
	Add Rows( 3 ),
	New Column( "name",
		Character,
		Nominal,
		Set Values( {"ALICE", "NEW_NAME", "OTHER_NEW_NAME"} )
	),
	New Column( "height",
		Continuous,
		Set Values( [999, 999, 999] )
	),
	New Column( "weight",
		Continuous,
		Set Values( [999, 999, 999] )
	),
	New Column( "RANK",
		Continuous,
		Set Values( [3, 1, 2] )
	),
	New Column( "CODE",
		Continuous,
		Set Values( [0, 1, 1] )
	),
	invisible
);

dt_joined = dt << Join(
	With(dt_new),
	Merge Same Name Columns,
	By Matching Columns(:name = :name),
	Drop multiples(0, 0),
	Include Nonmatches(1, 1),
	Preserve main table order(1),
	Output table("JOINED_TABLE")
);
Close(dt, no save);
Close(dt_new, no save);

[#8]

I wrote a wish to add an option to update the main table:

Tables join: option to update main table 

feel free to support it

[#9]

The new Join Platform could look like this:
https://community.jmp.com/t5/JMP-Wish-List/Tables-join-option-to-update-main-table/idc-p/653857/high...