Error when updating table in Sybase

Question

Hi, I am issuing an UPDATE Statement on a Sybase table but I am getting the below error:

Msg 325, Level 18, State 4: Server 'dev', Line 1: Adaptive Server finds no legal query plan for this statement. If an Abstract Plan is forcing the query plan, check its correspondence to the query. If not, please contact Sybase Technical Support.

Here is my UPDATE Statement.

 Update TABLE1 SET SAMPLECOL = (
   Select
   TABLE2.SAMPLECOL
   from TABLE2
   where
   TABLE2.COMMON_ID = TABLE1.COMMON_ID
 )
 where
 TABLE1.TABLE1_ID in (
   Select
   TABLE1.TABLE1_ID
   from TABLE1
   inner join TABLE2
   on TABLE1.COMMON_ID = TABLE2.COMMON_ID
   where TABLE1.SAMPLECOL = ''
 )

Any insights will be greatly appreciated.

Answer 1

I THINK the problem is that you are setting the column SAMPLECOL with something which could return multiple values. Try doing this and post the result back here:

Update TABLE1 SET SAMPLECOL = (
   set rowcount 1
   Select 
   TABLE2.SAMPLECOL
   from TABLE2
   where
   TABLE2.COMMON_ID = TABLE1.COMMON_ID
   set rowcount 0
 )

(this will select only one row record and your logic might not work based on this, however as far as the error you are getting I am sure is related to this). Again try it and let us know the outcome.

Answer 2

try instead;

update TABLE1
   set SAMPLECOL = T2.SAMPLECOL
  from TABLE1 T1, TABLE2 T2
 where T1.COMMON = T2.COMMON
   and isnull(T1.SAMPLECOL, '') = ''