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 Multiple Values

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

Active User


Joined: 16 Apr 2009
Posts: 151
Location: India

PostPosted: Mon Apr 05, 2010 10:14 pm    Post subject: Update Multiple Values
Reply with quote

Hi!
How to handle -811 during update?
Code:
UPDATE <Table1 t1>                             
   SET <t1.Col1> = (SELECT <t2.Col1>                               
                      FROM <Table2 t2>                                                 
                     WHERE t2.Col1 != 'ERT');

I have made the SET clause as 'equal to' predicate.
I'm getting more than one value from sub query.

I have to update two rows in table 1 with the two different values from table2.

How can I do it?
Back to top
View user's profile Send private message

CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Mon Apr 05, 2010 10:22 pm    Post subject:
Reply with quote

HameedAli wrote:
I have to update two rows in table 1 with the two different values from table2.
How do you differentiate between which row gets which value?
Back to top
View user's profile Send private message
HameedAli

Active User


Joined: 16 Apr 2009
Posts: 151
Location: India

PostPosted: Mon Apr 05, 2010 10:55 pm    Post subject: Reply to: Update Multiple Values
Reply with quote

By applying values for the key columns of table 1, I differentiate which row got which value.

I perform a join on table 2 to table 1 to understand the values.
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Mon Apr 05, 2010 11:09 pm    Post subject: Re: Reply to: Update Multiple Values
Reply with quote

HameedAli wrote:
By applying values for the key columns of table 1, I differentiate which row got which value.
I perform a join on table 2 to table 1 to understand the values.
Then apply those same values on table 1.
Maybe a WHERE for t1 in addition to the WHERE for t2?
Back to top
View user's profile Send private message
HameedAli

Active User


Joined: 16 Apr 2009
Posts: 151
Location: India

PostPosted: Mon Apr 05, 2010 11:18 pm    Post subject: Reply to: Update Multiple Values
Reply with quote

Code:
UPDATE <Table1 t1>, <Table2 t21>                             
   SET <t1.Col1> = (SELECT <t2.Col1>                               
                      FROM <Table2 t2>                                                 
                     WHERE t2.Col1 != 'ERT')
   where <t21.Col2> in (123,321,111);

two tables in update clause isn't possible, right?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Mon Apr 05, 2010 11:37 pm    Post subject:
Reply with quote

Code:
UPDATE <Table1>
   SET Col1 = (SELECT Col1                               
                      FROM Table2                                                 
                     WHERE Col1 NOT = 'ERT')
                         AND Col2 in (123,321,111);


you were receiving -811 because you original WHERE clause in the subselect
returned more than one row.

and NO, you can not UPDATE multiple tables in a single SQL

if the additional WHERE condition of COL2 reduces your result set to 1 row,
then the above code will work.

in the future, suggest that you
  1. not use != and instead use NOT =
  2. determine what WHERE conditions only return one row by SPUFIing a simple select based on your subquery
  3. when it is not required do not use table and column qualification


you are aware that you will be setting all rows in TABLE1??

and last but not least 'UPDATING MULTIPLE VALUES'
is really a poor Topic subject.
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 Sorting of hex values Saurabh_mi DFSORT/ICETOOL 11 Thu May 25, 2017 3:49 pm
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 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


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