Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Update first 100 rows only using SPUFI

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Update first 100 rows only using SPUFI
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    Post subject:
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    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Conditional highlighting of rows in I... packerm CLIST & REXX 2 Thu Sep 21, 2017 6:50 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us