cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
TWE
TWE
Level III

UPDATE JOIN SQL statement with JMP tables

Hi,

 

I would like to update via SQL statement the :age column in dt1 with the values of dt2. 

dt1 = Open( "$SAMPLE_DATA/Big Class.jmp", output table("t1"));
dt2 = subset(dt1);
dt2:age[1] = 99;

Query(
	Table( dt1, "t1" ),
	Table( dt2, "t2" ),
	"\[
		UPDATE t1
		SET 
			t1.age = t2.age
		FROM
			t1
		INNER JOIN 
			t2 
		ON 
			t1.name = t2.name
		
	]\"
);

I got an syntax error near "."

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
ih
Super User (Alumni) ih
Super User (Alumni)

Re: UPDATE JOIN SQL statement with JMP tables

I'm not sure if JMP understands SQL related to UPDATE queries, based on this statement failing with "Query failed: table t1 may not be modified in access or evaluation..." I'm not sure this is possible.

 

Query(
	Table( dt1, "t1" ),
	"UPDATE t1 SET age = 10"
);

You can, however, do this with Update:

dt1 << Update(
	With( dt2 ),
	Match Columns( :name = :name ),
	Replace columns in main table(:age)
);

If you have a more complex sql statement, you could create a SELECT query first using SQL to create a new table, then use this update function to update your original table.

 

 

View solution in original post

2 REPLIES 2
ih
Super User (Alumni) ih
Super User (Alumni)

Re: UPDATE JOIN SQL statement with JMP tables

I'm not sure if JMP understands SQL related to UPDATE queries, based on this statement failing with "Query failed: table t1 may not be modified in access or evaluation..." I'm not sure this is possible.

 

Query(
	Table( dt1, "t1" ),
	"UPDATE t1 SET age = 10"
);

You can, however, do this with Update:

dt1 << Update(
	With( dt2 ),
	Match Columns( :name = :name ),
	Replace columns in main table(:age)
);

If you have a more complex sql statement, you could create a SELECT query first using SQL to create a new table, then use this update function to update your original table.

 

 

TWE
TWE
Level III

Re: UPDATE JOIN SQL statement with JMP tables

This works.

Many thanks!