MonetDBLite Left Join Syntax

Question

Working in R and trying to add a column to an existing MonetDBLite table by running a left join with a second table using the following code:

dbSendQuery(mdb, "UPDATE table1 
   SET table1.variable = table2.variable 
   FROM table1  LEFT JOIN table2 ON table1.identifier = table2.identifier;")

Returns the error:

Server says 'syntax error, unexpected '.', expecting '=' in: "update table1 
   set table1."

Does MonetDB not support the dot delimiter to reference a field within a table? Many thanks for any insights.

Thanks for the quick response. But no luck with that. Throws the error: "Server says 'syntax error, unexpected FROM, expecting SCOLON in: "update table1 set variable = table2.variable f" — charlie, Nov 09 '16 at 16:10

score 1 · Answer 1 · answered Nov 16 '16 at 13:46

Came up with a work around that involves creating a third table rather than updating an existing table, then dropping the original table. (Pretty sure there is a more elegant way to do this, but...)

dbSendQuery(db, "create table table3 as
select a.*,
b.variable
from table1 as a
left join table2 as b
on 
(a.identifier = b.identifier);")

dbRemoveTable(db, "table2")

MonetDBLite Left Join Syntax

1 Answers1