IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Update first 100 rows only using SPUFI


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Sachin Ghadage

New User


Joined: 06 Nov 2009
Posts: 3
Location: pune

PostPosted: Fri Nov 06, 2009 12:36 pm
Reply with quote

Hi,

I would like to write query in such a way that:

It will update first 100 rows out of 500 rows selectd.

For e.g.

Update Table_Name first 100 only
set Err_Ind= 'Y'
where userid= 'ABC';

Here Where clouse will selct 500 rows but out of that I have to update first 100 .
Whats a correct syntax?.. is it possible?
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Fri Nov 06, 2009 1:00 pm
Reply with quote

You need to tell the order or else what you are saying is meaningless in RDBMS.
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Fri Nov 06, 2009 6:09 pm
Reply with quote

Hi Sachin,

I am not sure how we do using a single query, but we can do it using mutliple queries using Session tables in SPUFI

Steps are as followed:

1) Create a temperory session table with required columns.
2) Insert with select into the session tables.(Multi Insert)
3) Do an update against the temporary session table.
4) Finally update the actual tables joining the session table.

Sequence should be in this order

Ex:
Consider session table is created and record is inserted and you are trying to change the Designation from trainee to Programmer.

Actual TB
Emp ID Emp Nm Emp Desg
--------------------------------------
01 A Trainee
02 B Trainee

Session TB
Emp ID Emp Nm Emp Desg
--------------------------------------
01 A Trainee
02 B Trainee

First change the record in Session table to Programmer

Session Table after update
Emp ID Emp Nm Emp Desg
--------------------------------------
01 A Programmer
02 B Programmer

Finally,

Update status in actual table joining the employee id's between the master and session table.

The whole set of queries should be executed in the correct sequence for this to work.
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Fri Nov 06, 2009 6:10 pm
Reply with quote

While inserting the record to session table using insert with select do a fetch first 100 records only
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts DELETE SPUFI DB2 1
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Adding QMF and SPUFI to the ISPF menu DB2 20
No new posts Exclude rows with > than x occurre... DFSORT/ICETOOL 6
No new posts Convert single row multi cols to sing... DFSORT/ICETOOL 6
Search our Forums:

Back to Top