|
View previous topic :: View next topic
|
| Author |
Message |
r arunmoezhi
New User
Joined: 26 Jun 2009
Posts: 32
Location: Chennai
|
|
|
|
Hi,
I have an update query which is currently running in production. I need to understand the logic behind the query.
| Code: |
UPDATE TABLE_A
SET TAB_A_COL1 =
(
SELECT DISTINCT TAB_B_COL1 FROM TABLE_B
WHERE TAB_B_COL2='XYZ'
)
WHERE TAB_A_COL2='ABC'
AND EXISTS
(
SELECT 'Y' FROM TABLE_B
WHERE TAB_B_COL2='XYZ'
)
|
A SELECT query on TABLE_B is performed twice with the same WHERE clause.Why?
What difference is the EXISTS making?
Thanks in advance. |
|
| Back to top |
|
 |
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
It will update the table A only if there is a row in the table B with
TAB_B_COL2 = 'XYZ'.
If you remove the EXISTS clause, then it will update table A even if there is no row in table B with TAB_B_COL2 = 'XYZ' |
|
| Back to top |
|
|
r arunmoezhi
New User
Joined: 26 Jun 2009
Posts: 32
Location: Chennai
|
|
|
|
Got what you meant.
But this inner query
| Code: |
SELECT 'Y' FROM TABLE_B
WHERE TAB_B_COL2='XYZ'
|
is executed twice. Is there a way of optimization where we can make this query to run only once without affecting the existing logic. |
|
| Back to top |
|
|
Lijo
New User
Joined: 24 Aug 2008
Posts: 41
Location: Cochin
|
|
|
|
| Quote: |
| If you remove the EXISTS clause, then it will update table A even if there is no row in table B with TAB_B_COL2 = 'XYZ' |
If there is no row in Table B for 'XYZ', then how will you get the value for updating Table A?
| Quote: |
| What difference is the EXISTS making? |
If you want to see the difference, try executing this query with 'Exists' clause and without 'exists' clause.
If there is no row in Table B for XYZ, then with 'Exists' query will return +100. i.e. NO ROWS found for Update
But without 'exists' clause will return a -407 and results in SQL error in production. To avoid this situation your production query is having 'Exists' clause. |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
| r arunmoezhi wrote: |
Got what you meant.
But this inner query
| Code: |
SELECT 'Y' FROM TABLE_B
WHERE TAB_B_COL2='XYZ'
|
is executed twice. Is there a way of optimization where we can make this query to run only once without affecting the existing logic. |
I guess not. Is there any particular reason you want to tweak this query? I don't think this query might be taking much time to execute. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
The exists is just to make sure that your set query does not return null values and it might get updated in TAB_A_COL1. If you are sure that the query
SELECT DISTINCT TAB_B_COL1 FROM TABLE_B
WHERE TAB_B_COL2='XYZ'
is always going to return a value you can remove the EXISTS condition.
It will give -407 only if the column TAB_A_COL1 is defined as not null. If that is the case you can remove the EXISTS clause and handle -407 after the update query. |
|
| Back to top |
|
|
senthilssg
New User
Joined: 09 Dec 2005
Posts: 64
Location: USA
|
|
|
|
The above query could be split into 2 simple SQL. I It would help to do existence check first. If it exists, then do UPDATE on TABLE_A
SELECT DISTINCT TAB_B_COL1
INTO: WS-TAB-B-COL1
FROM TABLE_B
WHERE TAB_B_COL2='XYZ'
EVALUATE SQLCODE
WHEN 0
UPDATE TABLE_A
SET TAB_A_COL1 =: WS-TAB-B-COL1
WHERE TAB_A_COL2='ABC'
WHEN 100
....
...
WHEN OTHER
....
END-EVALUATE.
One more recommendation could be for first SQL in my response.
If you think that you are going to get only one value (but it contains duplicate rows) for column TAB_B_COL1 from TABLE_B for a given condition, you can use FETCH FIRST ROW ONLY instead of using DISTINCT. It would help to avoid internal SORT for DISTINCT.
SELECT TAB_B_COL1
INTO :WS-TAB-B-COL1
FROM TABLE_B
WHERE TAB_B_COL2='XYZ'
FETCH FIRST ROW ONLY
Please let us know your thoughts
Thanks
Senthil |
|
| Back to top |
|
|
|
|
