Query on SQL
Moderators: Kalicharan, Moderator Group
-
- Member
- Posts: 8
- Joined: Fri Sep 21, 2007 11:03 pm
Query on SQL
bolastuff1975@yahoo.co.uk
Hi Please, help me out in solving this issue.
i need to update field2 & field3 of table-1 when there are more than 2 rows with same key-2 and field1.
field2 & field3 are picked from table2 based on key-1 and max time stamp for t1.
Table structre and data is shown below. i have also shown the sql where i am not able to get a unique row.
please, note bothe the tables contain differnet fields for update, i have not listed them b'cos they do not form a part of key.
Table1
-------
Key1 char
Key2 char
field1
field2
field3
data in table1
----------------
key1 key2 fiedl1 field2 field3
----- ----- ------ ------- ------
101 54 1001 blabla blabla
102 54 1001 blabla blabla
103 54 1001 blabla blabla
104 54 1001 blabla blabla
105 55 1001 blabla blabla
106 50 1002 blabla blabla
table2
-------
key1
field1
field2
field3
timestamp
Date in table2
----------------
key1 field1 field2 field3 timestamp
101 1001 abc abc t1
101 1001 abc abc t2
102 1001 abc abc t3
102 1001 abc abc t31
103 1001 abc abc t4
104 1001 abc abc t5
sql
update table1
set (b.field3,b.field4)
=(select field1,field2)
from table1 a,table-2 b
where a.key1 = b.key1
and timestamp = (select max(ts) from table2 c
where c.key1 = b.key1)
)
where a.key1=?? i need to retreive a unique key.
i tried the below inner query but resulted in more than 1 row
where a.key1 = (select c.key1 from table1 c
where c.field1 = a.field1
and c.key2 in ( select key2 from table1 d
where d.key1 = c.key1
and field1 = c.field1
group by key2
having count(*) > 1))
Hi Please, help me out in solving this issue.
i need to update field2 & field3 of table-1 when there are more than 2 rows with same key-2 and field1.
field2 & field3 are picked from table2 based on key-1 and max time stamp for t1.
Table structre and data is shown below. i have also shown the sql where i am not able to get a unique row.
please, note bothe the tables contain differnet fields for update, i have not listed them b'cos they do not form a part of key.
Table1
-------
Key1 char
Key2 char
field1
field2
field3
data in table1
----------------
key1 key2 fiedl1 field2 field3
----- ----- ------ ------- ------
101 54 1001 blabla blabla
102 54 1001 blabla blabla
103 54 1001 blabla blabla
104 54 1001 blabla blabla
105 55 1001 blabla blabla
106 50 1002 blabla blabla
table2
-------
key1
field1
field2
field3
timestamp
Date in table2
----------------
key1 field1 field2 field3 timestamp
101 1001 abc abc t1
101 1001 abc abc t2
102 1001 abc abc t3
102 1001 abc abc t31
103 1001 abc abc t4
104 1001 abc abc t5
sql
update table1
set (b.field3,b.field4)
=(select field1,field2)
from table1 a,table-2 b
where a.key1 = b.key1
and timestamp = (select max(ts) from table2 c
where c.key1 = b.key1)
)
where a.key1=?? i need to retreive a unique key.
i tried the below inner query but resulted in more than 1 row
where a.key1 = (select c.key1 from table1 c
where c.field1 = a.field1
and c.key2 in ( select key2 from table1 d
where d.key1 = c.key1
and field1 = c.field1
group by key2
having count(*) > 1))
bolastuff1975,
Try this SQL
Contents of the first two selects of tables, before the updates
Table1
Table2
Contents of the second two selects of tables, after the update
Table1
Table2
Is this the result you were looking for? In the requirements at in your post you said ?more than 2 rows? which means 3 or more. If you wanted 2 or more change the ?> 2? to ?> 1?.
If you have any questions about the Update SQL, please do not hesitate to come back and ask.
Dave
Try this SQL
Code: Select all
DECLARE GLOBAL TEMPORARY TABLE TABLE1
(KEY1 CHAR(3)
,KEY2 CHAR(2)
,FIELD1 CHAR(4)
,FIELD2 CHAR(6)
,FIELD3 CHAR(6)
)
;
INSERT INTO SESSION.TABLE1 (KEY1, KEY2, FIELD1, FIELD2, FIELD3)
VALUES('101', '54', '1001', 'BLABLA', 'BLABLA');
INSERT INTO SESSION.TABLE1 (KEY1, KEY2, FIELD1, FIELD2, FIELD3)
VALUES('102', '54', '1001', 'BLABLA', 'BLABLA');
INSERT INTO SESSION.TABLE1 (KEY1, KEY2, FIELD1, FIELD2, FIELD3)
VALUES('103', '54', '1001', 'BLABLA', 'BLABLA');
INSERT INTO SESSION.TABLE1 (KEY1, KEY2, FIELD1, FIELD2, FIELD3)
VALUES('104', '54', '1001', 'BLABLA', 'BLABLA');
INSERT INTO SESSION.TABLE1 (KEY1, KEY2, FIELD1, FIELD2, FIELD3)
VALUES('105', '55', '1001', 'BLABLA', 'BLABLA');
INSERT INTO SESSION.TABLE1 (KEY1, KEY2, FIELD1, FIELD2, FIELD3)
VALUES('104', '50', '1002', 'BLABLA', 'BLABLA');
DECLARE GLOBAL TEMPORARY TABLE TABLE2
(KEY1 CHAR(3)
,FIELD1 CHAR(4)
,FIELD2 CHAR(6)
,FIELD3 CHAR(6)
,TS CHAR(5)
)
;
INSERT INTO SESSION.TABLE2 (KEY1, FIELD1, FIELD2, FIELD3, TS)
VALUES('101', '1001', 'A11', 'A22', 'T1 ');
INSERT INTO SESSION.TABLE2 (KEY1, FIELD1, FIELD2, FIELD3, TS)
VALUES('101', '1001', 'B11', 'B22', 'T2 ');
INSERT INTO SESSION.TABLE2 (KEY1, FIELD1, FIELD2, FIELD3, TS)
VALUES('102', '1001', 'C11', 'C22', 'T3 ');
INSERT INTO SESSION.TABLE2 (KEY1, FIELD1, FIELD2, FIELD3, TS)
VALUES('102', '1001', 'D11', 'D22', 'T31 ');
INSERT INTO SESSION.TABLE2 (KEY1, FIELD1, FIELD2, FIELD3, TS)
VALUES('103', '1001', 'E11', 'E22', 'T4 ');
INSERT INTO SESSION.TABLE2 (KEY1, FIELD1, FIELD2, FIELD3, TS)
VALUES('104', '1001', 'F11', 'F22', 'T5 ');
SELECT *
FROM SESSION.TABLE1
;
SELECT *
FROM SESSION.TABLE2
;
UPDATE SESSION.TABLE1 T1
SET (FIELD2,FIELD3) =
(SELECT FIELD2,FIELD3
FROM SESSION.TABLE2 T2
WHERE T2.KEY1 = T1.KEY1
AND T2.TS =
(SELECT MAX(TS)
FROM SESSION.TABLE2 T2A
WHERE T2A.KEY1 = T1.KEY1
)
)
WHERE EXISTS
(SELECT COUNT(T1A.KEY2)
FROM SESSION.TABLE1 T1A
WHERE T1A.KEY2 = T1.KEY2
AND T1A.FIELD1 = T1.FIELD1
HAVING COUNT(T1A.KEY2) > 2
)
;
SELECT *
FROM SESSION.TABLE1
;
SELECT *
FROM SESSION.TABLE2
;
Table1
Code: Select all
+----------------------------------------+
| KEY1 | KEY2 | FIELD1 | FIELD2 | FIELD3 |
+----------------------------------------+
1_| 101 | 54 | 1001 | BLABLA | BLABLA |
2_| 102 | 54 | 1001 | BLABLA | BLABLA |
3_| 103 | 54 | 1001 | BLABLA | BLABLA |
4_| 104 | 54 | 1001 | BLABLA | BLABLA |
5_| 105 | 55 | 1001 | BLABLA | BLABLA |
6_| 104 | 50 | 1002 | BLABLA | BLABLA |
+----------------------------------------+
Code: Select all
+-----------------------------------------+
| KEY1 | FIELD1 | FIELD2 | FIELD3 | TS |
+-----------------------------------------+
1_| 101 | 1001 | A11 | A22 | T1 |
2_| 101 | 1001 | B11 | B22 | T2 |
3_| 102 | 1001 | C11 | C22 | T3 |
4_| 102 | 1001 | D11 | D22 | T31 |
5_| 103 | 1001 | E11 | E22 | T4 |
6_| 104 | 1001 | F11 | F22 | T5 |
+-----------------------------------------+
Table1
Code: Select all
+----------------------------------------+
| KEY1 | KEY2 | FIELD1 | FIELD2 | FIELD3 |
+----------------------------------------+
1_| 101 | 54 | 1001 | B11 | B22 |
2_| 102 | 54 | 1001 | D11 | D22 |
3_| 103 | 54 | 1001 | E11 | E22 |
4_| 104 | 54 | 1001 | F11 | F22 |
5_| 105 | 55 | 1001 | BLABLA | BLABLA |
6_| 104 | 50 | 1002 | BLABLA | BLABLA |
+----------------------------------------+
Code: Select all
+-----------------------------------------+
| KEY1 | FIELD1 | FIELD2 | FIELD3 | TS |
+-----------------------------------------+
1_| 101 | 1001 | A11 | A22 | T1 |
2_| 101 | 1001 | B11 | B22 | T2 |
3_| 102 | 1001 | C11 | C22 | T3 |
4_| 102 | 1001 | D11 | D22 | T31 |
5_| 103 | 1001 | E11 | E22 | T4 |
6_| 104 | 1001 | F11 | F22 | T5 |
+-----------------------------------------+
If you have any questions about the Update SQL, please do not hesitate to come back and ask.
Dave
-
- Member
- Posts: 8
- Joined: Fri Sep 21, 2007 11:03 pm
bolastuff1975,
So, your getting a -811.
The ?WHERE EXISTS? returns only a ?True|False? indicator and will not give you a -811,
Nor will the innermost SET ?SELECT MAX(TS)?. There can only be one MAX timestamp, even if multiple rows have this timestamp. Therefore it must be giving you the -811 in the SET outer ?SELECT? This will occur if you have multiple rows on Table2 with the same KEY1 and Timestamp.
What version of DB2 are you using ?
So, your getting a -811.
The ?WHERE EXISTS? returns only a ?True|False? indicator and will not give you a -811,
Nor will the innermost SET ?SELECT MAX(TS)?. There can only be one MAX timestamp, even if multiple rows have this timestamp. Therefore it must be giving you the -811 in the SET outer ?SELECT? This will occur if you have multiple rows on Table2 with the same KEY1 and Timestamp.
What version of DB2 are you using ?
bolastuff1975,
I agree with Dave..explanation for resulting in more than one row in the
co-realted sub-query.
Even the below query what you have given in your initial post.
update table1
set (b.field3,b.field4)
=(select field1,field2)
from table1 a,table-2 b
where a.key1 = b.key1
and timestamp = (select max(ts) from table2 c
where c.key1 = b.key1)
)
Assume the below data for 2 fields in question
KEY1 TS
100 100
100 100
100 99
In this scenario for max(TS) for a key1=100 the query will give the
first 2 rows , the only scenario under which you will get a unique row is
only when you have a distinct value for a TS field.
Now what is important is
1) Whether more than one rec can have the same TS in PRODUCTION
or live scenario...may be your test data has 2 rows with same TS.
You need to cross check it, I blive that if you have different timestamps
then it will result in only one row.
2) If yes you can have same TS for more than one row
Then its just a thought what you can possibly do ,if your
requirement allows is add equality of one more field
say FIELD-NEW which has a distinct values for a max(TS)
where a.key1 = b.key1
and timestamp = (select max(ts) from table2 c
where c.key1 = b.key1
and C.FIELD-NEW = B.FIELD-NEW)
)
3) If TS will not be same for more than one rec then your intial query
should be good.
Please let us know how you have finally handled it.
Thanks,
Veera.
I agree with Dave..explanation for resulting in more than one row in the
co-realted sub-query.
Even the below query what you have given in your initial post.
update table1
set (b.field3,b.field4)
=(select field1,field2)
from table1 a,table-2 b
where a.key1 = b.key1
and timestamp = (select max(ts) from table2 c
where c.key1 = b.key1)
)
Assume the below data for 2 fields in question
KEY1 TS
100 100
100 100
100 99
In this scenario for max(TS) for a key1=100 the query will give the
first 2 rows , the only scenario under which you will get a unique row is
only when you have a distinct value for a TS field.
Now what is important is
1) Whether more than one rec can have the same TS in PRODUCTION
or live scenario...may be your test data has 2 rows with same TS.
You need to cross check it, I blive that if you have different timestamps
then it will result in only one row.
2) If yes you can have same TS for more than one row
Then its just a thought what you can possibly do ,if your
requirement allows is add equality of one more field
say FIELD-NEW which has a distinct values for a max(TS)
where a.key1 = b.key1
and timestamp = (select max(ts) from table2 c
where c.key1 = b.key1
and C.FIELD-NEW = B.FIELD-NEW)
)
3) If TS will not be same for more than one rec then your intial query
should be good.
Please let us know how you have finally handled it.
Thanks,
Veera.
-
- Member
- Posts: 8
- Joined: Fri Sep 21, 2007 11:03 pm
-
- Member
- Posts: 8
- Joined: Fri Sep 21, 2007 11:03 pm
FREE TUTORIALS
Tutorials
Free tutorials from mainframegurukul
- JCL Tutorial
Covers all important JCL concepts. - Cobol Tutorial
This tutorials covers all Cobol Topics from STRING to COMP-3. - DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. - SORT Tutorial
This Tutorial covers all important aspects of DFSORT with examples - CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page