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
]\"
);<BR />I got an syntax error near "."
Thanks
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.
