Sybase optimizer creates a cursor for a DELETE query which then fails

Question

We are executing the following query using embedded SQL in C:

DELETE archive_table FROM archive_table arc, #arc_chunk loc WHERE arc.col = loc.col

Sybase's response is:

The DELETE WHERE CURRENT OF to the cursor 'C42' failed because the cursor is on a join.

The query is bewing constructed as a C string and then executed using EXECUTE IMMEDIATE in embedded SQL.

Is there a way to perform this DELETE without the Sybase optimizer creating a cursor (which fails) to execute it?

Answer 1

When using the delete target table in a from clause don't put an alias on it.

  DELETE archive_table 
  FROM archive_table, #arc_chunk loc 
  WHERE archive_table.col = loc.col

[EDIT]

Another possibility is to remove the need for a join and fold it all into the where clause.

DELETE archive_table 
WHERE EXISTS ( SELECT 1 FROM #arc_chunk loc WHERE archive_table.col = loc.col ) 

I am assuming that 'col' is the unique key for the row.