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 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 ejck thru rexx to multiple jobs insid... Susanta All Other Mainframe Topics 5 Tue Sep 19, 2017 1:39 pm
No new posts PLI - printing from multiple programs Pedro PL/I & Assembler 8 Fri Sep 08, 2017 6:36 am
No new posts Repeat part of record multiple times Learncoholic DFSORT/ICETOOL 4 Tue Aug 29, 2017 11:33 am
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 21 Sun Aug 27, 2017 10:35 pm
No new posts Match two files using multiple keys a... santoshks1987 SYNCSORT 11 Fri Aug 18, 2017 10:50 am

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