topic Re: Table Update: How to update only selected columns? in Discussions

Table Update: How to update only selected columns?

Monomorphist — Thu, 08 Jun 2023 16:32:58 GMT

This should be a quick one:

Table update: How to update only selected columns?

Data Table( dt1 ) << Update(
	With( Data Table( dt10 ) ),
	Match Columns( :SAMPLE = :SamID ),
	Add Columns from Update Table( None ),
    ### Update Selected Columns (:Type, :Method) ###
)

It's simple when done manually, but in the script? There's nothing in the scripting guide (only how to avoid adding columns). My various guesses and trials (eg. see inside ### in above script) do not work.

Re: Table Update: How to update only selected columns?

jthi — Mon, 27 Feb 2023 11:23:16 GMT

If you have JMP16 you can perform the action once manually and then get the script from enhanced log, most likely what you are looking for is Replace Columns in Main Table()

Data Table("Big Class") << Update(
	With(Data Table("Subset of Big Class")),
	Match Columns(:name = :name),
	Replace Columns in Main Table(:age)
);

Re: Table Update: How to update only selected columns?

Mauro_Gerber — Mon, 27 Feb 2023 14:00:35 GMT

HI, this will probably do what you are looking for

Names Default To Here( 1 );

// opens Table
dt1 = Open( "$SAMPLE_DATA/Big Class.jmp" );

//generate update Table
dt2 = dt1 << Clear Select << Select Rows( Index( 1, 8 ) ) << Subset( Selected Rows( 1 ), Selected columns only( 0 ) );

// delete contend
for(k=4,k<=ncols(dt1),k++,column(dt1,k)[(1 :: 8)]=.);

//select columns
dt1 << Clear select;
wait(0.1);
dt1 << Select Rows ( (3::5) );
wait(0.1);



// ### scrpit only with selected rows

// get selected rows
s_rows = dt1 << Get Selected Rows();

// create a new column with selected rows as 1
n_c1 = dt1 << new column();
n_c1[s_rows]=1;

// create a new column on the update table
n_c2 = dt2 <<  New Column("temp",Numeric,"Continuous",<<Set Initial Data( 1 ));

// update only where the 1 is present (selected rows only)
dt1 << Update(
	With( dt2 ),
	Match Columns( :name = :name, n_c1 = n_c2 ),
);

// delete the columns
dt1 << Delete Columns(n_c1);
dt2 << Delete Columns(n_c2);