Modify data table with Query() function

Question

Hi,

I'm working with Query() function for quick selections on big JMP tables.

I can't run the "update" command.

Is it possible?

I would like to do something like this:

Query( Table( dt, "t1" ), "UPDATE t1 SET Height = -1 Where Age = 12" );

Thanks.

txnelson · Answer

An update would work just fine

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA/big class.jmp" );

dt2 = New Table( "My Update", private,
	New Column( "Age", values( [12] ) ),
	New Column( "Height", values( [-1] ) )
);

dt = dt << Update(
	With( dt2 ),
	Match Columns( :age = :Age )
);

close(dt2, nosave);

tom_abramov · Answer

Thanks, Jim

With "select", Query() performs much faster than "get rows where".

I was wondering whether "update" will work as well.

txnelson · Answer

I am not a Query function expert, however the error returned when running your code seems to indicate that a Query can not update a table

Query failed: table t1 may not be modified{5} in access or evaluation of 'Query' , Query/*###*/(Table( dt, "t1" ), "UPDATE t1 SET Height = -1 Where Age = 12")

A simple way to do what you want in regular JSL would be

dt:Height[dt<<get rows where(:age==12)]=-1;