fetch first 5 rows only for update

[jongunnar]

Hi. I want to write a query that returns a set of rows from a table. All those rows should fulfill several requirements, as specified by the query's where clause, AND I want to get exactly 5 (or X) number of rows AND I want to set an UPDATE lock on those rows.

I don't care if those 5 rows are the first 5 rows in the table that meet the requirements in the where clause or if they are the last 5 rows. In fact, they can be ANY 5 rows in the table, if they meet the requirements. But I want that update lock and I don't want more rows than 5 to be returned from the database (for performance reasons, f.ex.).

Here's how the query could look like (but it just doesn't seem to work on DB2):

select empno from emp where deptno = 3 fetch first 5 rows only for update

The problem with this query in DB2 is that "fetch first" clauses can't be used with "for update" clauses.

Surely this is a simple and common problem for which there is a known solution for.

Any help would be greatly appreciated.

 

[nicsin]

I've never used DB" but try

select top 5 empno from emp where deptno = 3

 

[jongunnar]

No, I think that the syntax "top 5" is illegal for DB2. I also belive that this is the Oracle equivalant of DB2's "fetch first 5 rows only". But thanks anyway.

 

[Dimandja]

Assuming you have the correct DB2 version (8?), then explore this approach:

UPDATE a-table SET b-field = some-value WHERE c-field IN (SELECT c-field FROM a-table WHERE d-field IS other-value FETCH FIRST 5 ROWS ONLY)

Dimandja

 

[jongunnar]

Thanks, but what I really want is doing FETCH FIRST 5 ROWS ONLY FOR UPDATE and that doesn't work for DB2. If I skip the FOR UPDATE clause, I run into concurrency problems.

 

[Dimandja]

Is there any reason why you wouldn't use a cursor?

Dimandja

 

[jongunnar]

I'm not sure I understand you here.

 

[Dimandja]

I am assuming you have worked with cursors?

A cursor will do what you want except returning 5 rows each time: you will be locking and updating 1 row at a time.

Is there something "magical" about 5 rows, 5 rows exactly?

The UPDATE statement I showed you will do what you request, if you can find the appropriate database engine.
With DB2 v. 8, you can use that UPDATE statement.

Is there any reason why it must be phrased as "FETCH FIRST 5 ROWS ONLY FOR UPDATE"?

Are any of these artificial limitations self-imposed or homework imposed?

Dimandja

 

[jongunnar]

Thanks for the continued help, Dimandja. You originally suggested the sql statement

UPDATE a-table SET b-field = some-value WHERE c-field IN (SELECT c-field FROM a-table WHERE d-field IS other-value FETCH FIRST 5 ROWS ONLY)

What I fear will happen with this query (and I might be wrong) is that read locks will be taken for the sub query, but probably update locks are required for concurrent applications, because the result set from the subquery is supposed to be updated. If the subquery would end with FOR UPDATE, then update locks would be used. It's always wise to take update locks on rows that you know you will update later in the transaction.

And no, this is a part of a real application and not homework. The number 5 is a parameter and it can vary. I just took the number 5 as an example.

 

[Dimandja]

If the SELECT is for BROWSE access, this might work. I don't have access to a test system at the moment, but you could test this statemnt I suppose.

Dimandja

 

[jongunnar]

I actually want to first get 5 items from a table and then update those items.

 

[jongunnar]

For the record - my problem remains unsolved. Anyone got any further input?

 

[Dimandja]

Have you tested any of the suggestions?

You haven't told us why you must have 5 records exactly at a time? Why not 1 at a time? That may actually be what you need?

Have you looked into the cursor option?

I'm afraid, you will need to answer some of these questions in order to get the help you need.

Dimandja

 

[SuperProgrammer]

There is a very simple way to pull this off in DB2:

select row_a from table_a
where row_a in
(
select row_a
from
(
select row_a, rownumber() over(order by row_a) as row_number
from table_a
where row_b = 1 and row_c = 'abc'
) as temp
where row_number <= 5
)