Hi,
are queries that contain an ORDER BY inherently
non-updatable, or does it depend on , for example, the
column it is ordered for? Or, to put it this way, is this a
limitation of SQL Anywhere 8, or is it a limitation of the
SQL query itself?

Thanks for all advice,
Martin

It depends on what you are doing *and* what the setting of
ANSI_UPDATE_CONSTRAINTS is. See the V8 Help...

=====
Adaptive Server Anywhere Database Administration Guide
16. Database Options
Alphabetical list of options
ANSI_UPDATE_CONSTRAINTS
=====

Breck

On 8 Dec 2003 01:58:45 -0800, Martin Sahm wrote:

--
bcarter (AT) risingroad (DOT) com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com

In article <3fd44cfa.1f6d.846930886 (AT) sybase (DOT) com>, Martin Sahm says...
IME, a simple "select * from x order by y" is updateable if the ORDER
BY can be resolved by an index. Other cases may work as well,
depending on the complexity of the statement.

--
Dave Kerber
Fight spam: remove the ns_ from the return address before replying!

REAL programmers write self-modifying code.

Whether or not any cursor is updatable is independent of the
optimization strategy chosen by the optimizer.

The *semantics* of the UPDATE operation, however, can depend on the type
of cursor specified by the application. For example, consider a plan
that utilizes only nested-loop join and indexed retrieval. This plan
will be SENSITIVE to concurrent updates (by the same application or
other connections) since the plan does not materialize any intermediate
or final result. On the other hand, materializing plan operators such as
hash join, which also do not support backwards scrolling, cannot be
sensitive to updates since the result of the join is materialized. To
ensure consistent behaviour across the board, ASA8 and ASA9 will:

- disallow positioned UPDATEs to joins when ANSI_UPDATE_CONSTRAINTS =
'CURSORS' or 'STRICT'. If set to OFF, an update over a join (say) will
be permitted.
- alter the cursor type from the one asked for to one that supports
predictable update semantics at the time of the OPEN CURSOR request (a
warning is returned to the application should this occur). Typically,
this is done when an application opens a default (ASENSITIVE) cursor
that is (again by default) updateable - ASA will turn this cursor into a
KEYSET-DRIVEN cursor to ensure predictable semantics.

You can see the effects of both of these if you look at the graphical
plans for queries in DBISQL.

Glenn

David Kerber wrote:
--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer