|
View previous topic :: View next topic
|
| Author |
Message |
jramuk
New User
Joined: 14 Nov 2007
Posts: 18
Location: US
|
|
|
|
Hi,
I have the following requirement
To update the column DESIG from the table TPIE.with the value from column DESPI from table TBAL
Conditions:
1. column NUMPI from TPIE and TBAL should be same
2. column TYPPI from TPIE is not equal to ABC
3. column C.DESIG <> A.DESPI
I used the following query
UPDATE XYZ.TPIE C
SET C.DESIG = (SELECT DISTINCT A.DESPI FROM XYZ.TBAL A
WHERE C.NUMPI = A.NUMPI
AND C.TYPPI <> 'ABC' AND C.DESIG <> A.DESPI)
WHERE C.NUMPI IN (SELECT D.NUMPI FROM XYX.TBAL D
WHERE C.TYPPI <> 'ABC' AND C.DESIG <> D.DESPI);
but this runs for a long time and gives -905 due to ASUTIME. I guess i need to optimize the query. Could you let me know if there is an alternate way? |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
What does the output from an EXPLAIN show?
How many total rows will this query have to "touch" to process?
How many of the columns named are a key or the high-order part of a key? |
|
| Back to top |
|
|
Simone Salzmann
New User
Joined: 26 Nov 2012
Posts: 17
Location: Switzerland
|
|
|
|
Two questions:
1) If there are numeric columns involved on both tables - do they have the same format?
2) Why Select Distinct?
It looks as if NUMPI may be "ambiguous" on table TBAL - what if the different occurences have different values for DESPI?
But if this is ok I'd shorten your query even like this (provided there aren't one million equals and only 10 unequals for DESPI and DESIG)
UPDATE XYZ.TPIE C
SET C.DESIG = (SELECT DISTINCT A.DESPI FROM XYZ.TBAL A
WHERE C.NUMPI = A.NUMPI)
WHERE C.TYPPI <> 'ABC'; |
|
| Back to top |
|
|
jramuk
New User
Joined: 14 Nov 2007
Posts: 18
Location: US
|
|
|
|
How many total rows will this query have to "touch" to process?
100000 in TBAL and 400000 in TPIE
How many of the columns named are a key or the high-order part of a key?
The Key in both the tables are different. Only NUMPI and TYPPI are common fields. |
|
| Back to top |
|
|
jramuk
New User
Joined: 14 Nov 2007
Posts: 18
Location: US
|
|
|
|
| Quote: |
| How many total rows will this query have to "touch" to process? |
100000 in TBAL and 400000 in TPIE
| Quote: |
| How many of the columns named are a key or the high-order part of a key? |
The Key in both the tables are different. Only NUMPI and TYPPI are common fields.
Quote'd |
|
| Back to top |
|
|
Simone Salzmann
New User
Joined: 26 Nov 2012
Posts: 17
Location: Switzerland
|
|
|
|
What I don't grab from your answer:
On what positions do NUMPI and TYPPI stand in the keys of the tables?
And do they have the same format? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Simone Salzmann wrote: |
| Code: |
UPDATE XYZ.TPIE C
SET C.DESIG = (SELECT DISTINCT A.DESPI FROM XYZ.TBAL A
WHERE C.NUMPI = A.NUMPI)
WHERE C.TYPPI <> 'ABC'; |
|
This will
- set C.DESIG to NULL when there are no qualifying rows in xyz.TBAL.
=> You repeat the subquery in the where clause to avoid this.
- update rows on TPIE even if C.DESIG is already the correct value.
=> you add AND C.DESIG <> A.DESPI to the where clause of the subquery to avoid this.
So the original query was probably more correct then this one. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
* Criteria on the outer table should remain on the outer where clause
and
* i would try it using exists :
| Code: |
UPDATE XYZ.TPIE C
SET C.DESIG = (SELECT A.DESPI FROM XYZ.TBAL A
WHERE C.NUMPI = A.NUMPI
AND C.DESIG <> A.DESPI
fetch first 1 row only)
WHERE C.TYPPI <> 'ABC'
AND exists (SELECT A.DESPI FROM XYZ.TBAL A
WHERE C.NUMPI = A.NUMPI
AND C.DESIG <> A.DESPI)
; |
and you do need an index on TBAL(NUMPI,...) |
|
| Back to top |
|
|
Simone Salzmann
New User
Joined: 26 Nov 2012
Posts: 17
Location: Switzerland
|
|
|
|
Hi, GuyC
I could live with updating equal fields, that's why I left out the <> condition.
But it's true *blush* while thinking about the DISTINCT clause I forgot my thoughts about non-matching rows on the tables. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
If updating non-equal fields already gives -905, how about updating all rows?
Suppose this is a correction query which would update 50 rows.
You wouldn't mind updating 400.000 rows just so you can save 3 lines of coding?
How about last update timestamps, mirrorring, triggers, .....
I thought your ambition was to become DBA. (edit <= oops, it was someone elses) |
|
| Back to top |
|
|
|
|
