The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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')

asked 13 Jul '10, 16:08

Daniel's gravatar image

Daniel
16112
accept rate: 0%

edited 08 Dec '15, 05:11

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644

I would think that syntax should be working in SQLA too (at least I noticed nothing incompatible), identical schma assumed. Did you give it a try?

(13 Jul '10, 16:33) Reimer Pods
Comment Text Removed
Comment Text Removed

Yes, I gave it a try, but I think that there is no 'over (partion by xx)' or similar contruct in Adaptive Server 12. Ok, by now I realised that Adaptive Server and SQL Anywhere are different products. I am sorry I asked on the wrong site.

(14 Jul '10, 08:33) Daniel
Comment Text Removed

Could someone delete the entire question. I thought this was a site for ASE and didnt know the difference to Sybase Anywhere. Sorry for that. I will repost on StackOverflow.

(14 Jul '10, 08:43) Daniel

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%20Paulley's gravatar image

Glenn Paulley
10.7k568104
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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×47
×6

question asked: 13 Jul '10, 16:08

question was seen: 1,042 times

last updated: 08 Dec '15, 05:11