|
View previous topic :: View next topic
|
| Author |
Message |
Learncoholic
New User
Joined: 20 Sep 2007
Posts: 97
Location: India
|
|
|
|
Hi,
I have a table TABLE1 that has 3 columns ID,AMT1,AMT3. The ID column is not unique. Now I want to add AMT1 & AMT2 values for each unique ID value in TABLE1 & populate it into a new table TABLE2. Thus TABLE2 will have unique ID value & AMT3 & AMT4 columns holding the sum of AMT1 & AMT2 respectively.
To explain with an example: Consider TABLE1 has the following 3 entries:
| Code: |
ID AMT1 AMT2
100 5 4
100 4 2
200 3 9
|
After execution of the said query, TABLE2 will have the following entries:
| Code: |
ID AMT3 AMT4
100 9 6
200 3 9
|
Also TABLE1 has many ID out of which we select a required few.
Please let me know if the following query will work:
| Code: |
UPDATE TABLE1 T1 SET (T1.AMT3,T1.AMT4) =
(
SELECT VAL1,VAL2
FROM (
SELECT T2.ID,SUM(T2.AMT1) AS VAL1,SUM(T2.AMT2) AS VAL2
FROM TABLE2 T2
WHERE T2.ID IN (
SELECT T3.ID
FROM TABLE3 T3
WHERE T3.C_CODE = '300'
)
GROUP BY T2.ID
)
)
WHERE T1.ID = T2.ID |
TABLE3 has unique values for ID. |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
What happened when you tried this?
My intuition says this will not work. . . |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
I'm pretty sure it won't work: can't use T2-fields in the outer where clause
and if it could it probaly would have a terrible response, but I won't even bother to try |
|
| Back to top |
|
|
Learncoholic
New User
Joined: 20 Sep 2007
Posts: 97
Location: India
|
|
|
|
Hi,
Sorry for the last reply.
This SQL threw an error as below:
| Code: |
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "<EMPTY>". SOME SYMBOLS THAT
MIGHT BE LEGAL ARE: CORRELATION NAME |
I believe this error is due to the reason that GUYC stated.
Please let me know if this can be handled using a query.
Thanks |
|
| Back to top |
|
|
Learncoholic
New User
Joined: 20 Sep 2007
Posts: 97
Location: India
|
|
|
|
last reply should have been late reply.  |
|
| Back to top |
|
|
Learncoholic
New User
Joined: 20 Sep 2007
Posts: 97
Location: India
|
|
|
|
Hi,
Just another query.
Is there an way of selecting specific columns that I intend to update using an UPDATE query out of a list of columns. For example:
| Code: |
UPDATE TABLE1 SET(COL1,COL2) =
(
SELECT COL3,COL4,COL5
FROM TABLE2
WHERE COL6 = '100'
)
WHERE COL1 > COL2 |
Here the inner SELECT has more than 2 columns listed. Out of them I intend to update the values of two columns only.
Is there a way of doing this?
Thanks |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
This should work :
| Code: |
UPDATE TABLE1 T1 SET (AMT3,AMT4) =
(SELECT SUM(T2.AMT1) ,SUM(T2.AMT2)
FROM TABLE2 T2
where T2.ID = T1.ID
)
WHERE exists (select 1 from table3 where T3.C_CODE = '300' and T3.ID = T1.ID) |
or
| Code: |
...
WHERE T1.ID IN (select T3.ID from table3 where T3.C_CODE = '300') |
Exists or IN-list : what is faster depends on clustering, indexes and #qualifying rows.
Usually Exists is faster |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Learncoholic wrote: |
Here the inner SELECT has more than 2 columns listed. Out of them I intend to update the values of two columns only.
Is there a way of doing this?
|
No, SET (collist) = (updatecollist) must have same numbers of columns
why would you select more than you need ? |
|
| Back to top |
|
|
Learncoholic
New User
Joined: 20 Sep 2007
Posts: 97
Location: India
|
|
|
|
Hi GuyC,
The query you have supplied worked perfectly well as intended.
Thanks you very much. |
|
| Back to top |
|
|
|
|
