IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Update option in join


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
banand

New User


Joined: 05 Jun 2007
Posts: 28
Location: Mumbai

PostPosted: Tue Jul 07, 2009 2:53 pm
Reply with quote

Hi all,

Can we use update in inner join??

Actually I want to set the number field of table A to zero.
The key value of the table A and table B should be equal and also name field of the table B should be 'C'

Please tell me whether it is possible and also let me know whether any more information is required.

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

Global Moderator


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

PostPosted: Tue Jul 07, 2009 3:11 pm
Reply with quote

joins tend to generate a read-only results.

why not use a subquery?

if that is too slow, you might think about unload, modify qsam, load.
Back to top
View user's profile Send private message
banand

New User


Joined: 05 Jun 2007
Posts: 28
Location: Mumbai

PostPosted: Tue Jul 07, 2009 3:50 pm
Reply with quote

Hi Actually we are given a query to optimize and make it cost efficient

that query contains exists option... can you tell us any other option to optimize the query?

plaese find the below query that was given to us

EXEC SQL UPDATE ORDER01 SET PVTTLCL = 0
WHERE EXISTS
(SELECT FFNDCD FROM PART03
WHERE FFNDFMLY = :PART03.FFNDFMLY
AND PFNDCD = FFNDCD)
END-EXEC

FIELD NAME TABLE NAME

PVTTLCL ORDER01
FFNDCD PART03
FFNDFMLY PART03
PFNDCD ORDER01

Thanks,
Chaitanya.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Jul 07, 2009 8:05 pm
Reply with quote

Hello,

Quote:
Can we use update in inner join??
Quote:
Hi Actually we are given a query to optimize and make it cost efficient
that query contains exists option... can you tell us any other option to optimize the query?

Which is it? These don't appear to be related. . . icon_confused.gif
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Jul 07, 2009 8:25 pm
Reply with quote

Use EXPLAIN to start with.

number of rows in both tables;
are all columns in the subquery (for both tables) in an index?

you could look at the other threads in this forum about query optimization.

db2 for strobe.
bmc query analyzer

list goes on, but you are the only one who knows about your environment.
we are wasting our time guessing.

you have to analyze the tables to determine what is going on.
talk to coworkers, your dba's.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Jul 09, 2009 7:54 pm
Reply with quote

Remove the correlation

Code:


UPDATE ORDER01 SET PVTTLCL = 0
WHERE PFNDCD IN
( SELECT FFNDCD FROM PART03
WHERE FFNDFMLY = :PART03.FFNDFMLY )


Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts SCOPE PENDING option -check data DB2 2
No new posts OUTFIL with SAVE option DFSORT/ICETOOL 7
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts CICS vs LE: STORAGE option CICS 0
No new posts Join 2 files according to one key field. JCL & VSAM 3
Search our Forums:

Back to Top