Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Update of multiple records

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
shell100

New User


Joined: 14 Apr 2006
Posts: 25
Location: Banglore,India

PostPosted: Wed May 10, 2006 7:17 pm    Post subject: Update of multiple records
Reply with quote

Hi ,

As per my requirement i have to update multile rows in table.
For ex i have two element A & B & table could have multiple rows with this combination as below:

A A A
B B B
C Z T

I have to update third field(C,Z,T) with some constant value(let say Y) for all the records having element A & B.

How can go for this. I think i have to use cursor with update. Please let me know how can i use that. Some hint would work......

Thanks,
Back to top
View user's profile Send private message

DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Wed May 10, 2006 10:11 pm    Post subject: Re: Update of multiple records
Reply with quote

Shell,

I'm sorry, but I don't understand your requirement.

Would you please post what you want the result to look like?

Thanks,

Dave
Back to top
View user's profile Send private message
shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 171
Location: Bangalore

PostPosted: Thu May 11, 2006 9:36 am    Post subject:
Reply with quote

Hi Shell,

I am also didn't understand your question, but you can try the following query to update one or more rows.

UPDATE table
SET column1 = ( SELECT col1 FROM .... ) ,
column2 = ( SELECT col2 FROM .... )
WHERE ....

or you can bracket the columns:

UPDATE table
SET ( column1 , column2 ) = ( SELECT col1, col2 FROM .... )
WHERE ...

in this way we can update more than one row at a time.


Is this is your requirement
Back to top
View user's profile Send private message
shell100

New User


Joined: 14 Apr 2006
Posts: 25
Location: Banglore,India

PostPosted: Thu May 11, 2006 10:05 am    Post subject: Update of multiple records
Reply with quote

FOR Example:

Name Class Record type
A B C
A B Z
A B T


Results Required :

Name Class Record type
A B Y
A B Y
A B Y

what i need to do is update Record Type as 'Y' where Name = A AND Class = B.
Back to top
View user's profile Send private message
shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 171
Location: Bangalore

PostPosted: Thu May 11, 2006 10:20 am    Post subject: Re: Update of multiple records
Reply with quote

Hi,

UPDATE TEST1
SET RECTYPE='Y'
WHERE NAME='A' AND CLASS='B';


Run this query. You will get your requirement.

Result of the query when I run

REPORT



NAME CLASS RECTYPE
---- ----- -------
A B Y
A B Y
A B Y


*** END ***
Back to top
View user's profile Send private message
twissi

Active User


Joined: 01 Aug 2005
Posts: 105
Location: Somerset, NJ

PostPosted: Thu May 11, 2006 5:49 pm    Post subject:
Reply with quote

Hi Shell,

In addition to all aswers given above, I would like to tell you that, you don't have to use a CURSOR for updating a table. A straight forward query will update all records satisfying your condition in the WHERE clause.

(Mind you, this can also be accomplished using a CURSOR, but it causes multiple execution of the same query that could have been achieved in a single non-cursor query.)

Hope this makes sense!

Cheers, Twissi.
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 Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
This topic is locked: you cannot edit posts or make replies. SDSF multiple spool datasets extracte... PJAlarcon CLIST & REXX 1 Fri Apr 21, 2017 10:50 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us