Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In DB2, I need to do a SELECT FROM UPDATE, to put an update + select in a single transaction.
But I need to make sure to update only one record per transaction.

Familiar with the LIMIT clause from MySQL's UPDATE option

places a limit on the number of rows that can be updated

I looked for something similar in DB2's UPDATE reference but without success.

How can something similar be achieved in DB2?

share|improve this question
    
Simply include a WHERE condition on the primary key column(s). That ensures that only a single row will be updated. –  a_horse_with_no_name Jan 5 '12 at 21:35
    
Why did you repost the question: stackoverflow.com/questions/8735428/… –  a_horse_with_no_name Jan 5 '12 at 21:35
    
@a_horse_with_no_name looking for a wider "audience". –  Marius Butuc Jan 5 '12 at 22:43
1  
@MariusButuc - did you try the cursor with update? did it work? –  Chris Aldrich Feb 28 '12 at 14:09
    
@ChrisAldrich yes, thank you! –  Marius Butuc May 9 '12 at 15:05
add comment

1 Answer

up vote 2 down vote accepted

Actually, on the link you have, it mentions that you can update only one record, you just have to use cursors to do so. It then points you to the DECLARE CURSOR page.

So first, you would have to declare a cursor from your table with the select statement that would give you what you want.

DECLARE C1 CURSOR
FOR (SELECT * FROM MYTABLE WHERE...);

Move to the value of the record in the cursor that you want.

Then run you update statement against the cursor using the link you mention

UPDATE MYTABLE 
SET MYCOLUMN = ?
WHERE CURRENT OF C1;

I believe you need to issue a COMMIT (which closes the cursor unless you specify otherwise in the DECLARE CURSOR clause).

Check out the documentation in the link you have and in the one I have. You may end up having to use a stored procedure to run this.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.