update of #TEMP table resulting in error

[virtualdatabase]

Within a virtual procedure I insert INTO  a #TEMP table..

Then I update it and finally return the contents of the temp table..

When I query the proc I get this error:

UPDATE #TEMP1 SET ReportEvent = (SELECT event_id FROM clinical_event AS ce2 WHERE (ENCNTR_ID = #TEMP1.encntr_id) AND (event_class_cd = 104) LIMIT 2)" contains non-pushdown constructs and no compensating action can be taken as the table lacks a unique key or the source does not support equality predicates.

It appears that the planner is thinking #TEMP1 is a RDBMS table...

Any suggestions?

Thanks

[shawkins]

Correlated subqueries with temp table updates is a KI - http://jira.jboss.org/jira/browse/TEIID-1170 This could be allowed, but due to the current logic the subquery evaluation would hold the processing thread - which should generally be avoided.  The error message means that if you declare a primary key, then we should be able to compensate for the update using a select and then updates based upon the primary key.

Steve