topic Re: UPDATE JOIN SQL statement with JMP tables in Discussions

UPDATE JOIN SQL statement with JMP tables

TWE — Sat, 10 Jun 2023 23:18:17 GMT

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

Re: UPDATE JOIN SQL statement with JMP tables

ih — Tue, 25 Aug 2020 13:47:06 GMT

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.

Re: UPDATE JOIN SQL statement with JMP tables

TWE — Mon, 31 Aug 2020 06:23:54 GMT

This works.

Many thanks!