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 Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am


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