cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
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!