View previous topic :: View next topic
|
Author |
Message |
banand
New User
Joined: 05 Jun 2007 Posts: 28 Location: Mumbai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
banand
New User
Joined: 05 Jun 2007 Posts: 28 Location: Mumbai
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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. . . |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
Remove the correlation
Code: |
UPDATE ORDER01 SET PVTTLCL = 0
WHERE PFNDCD IN
( SELECT FFNDCD FROM PART03
WHERE FFNDFMLY = :PART03.FFNDFMLY )
|
|
|
Back to top |
|
|
|