SAP SQL Anywhere

Question

I have to update a column in a very, very large table. So performance is a big issue.

The question is the same as this one, but only for Sybase. Is there a way to do it without a self join like in the top voted answer for Oracle?

This was my first attempt, but it is by orders to slow for the table it is intended for:

UPDATE table SET flag = 1
FROM table AS a1
LEFT OUTER JOIN table AS a2
ON (a1.other = a2.other AND a1.id < a2.id)
WHERE a2.ID IS NULL
and a1.name in ('x', 'y')

Answer 1

1

I'm frankly a bit confused, because the StackExchange post doesn't address updates at all.

However - as far as I can tell, virtually any of the SQL queries given in the answers of that StackExchange post will work in SQL Anywhere. SQL Anywhere supports (of course) subqueries and joins to derived tables. SQL Anywhere 9.0.1 and up supports WINDOW functions, and you can use both MAX() and ROW_NUMBER() as window functions. Versions 10 and up supports the FIRST_VALUE() window function.

permanent link

answered 13 Jul '10, 19:45

Glenn Paulley
10.8k●5●76●106
accept rate: 43%

Comment Text Removed

I am sorry about the confusion. What I want to do is update a value, but the rows I want to update on are really the same as in the linked to question. I am using version 12.5.4 and will look further into your answer.

(14 Jul '10, 07:31) Daniel

Ah - you're using Sybase ASE 12.5.4. In that case, many of the solutions discussed in the StackExchange posting won't work, as ASE does not support WINDOW functions.

(14 Jul '10, 10:07) Glenn Paulley

Thanks Glenn. I wasnt aware of the different products. I will probably try it with an cursor next. The concern is, that the table must not be blocked for too long.

(14 Jul '10, 11:54) Daniel

flat view

SAP SQL Anywhere

How do I fetch the rows which have the Max value in a different column

Follow this question

Related questions