Query on SQL

[bolastuff1975]

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))

[DavidatK]

bolastuff1975,

Try this SQL

[bolastuff1975]

Dave,
when i try it here, i get a message taht the inner query is listing more than 1 row.

I beleive 'where exists' condition checks if KEY2 is satisfying our update criteria and results more than 1 key1 but I need to pick single row(KEY1) for updates.

Thanks

[DavidatK]

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 ?

[Veera]

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.

[bolastuff1975]

Dave & Veera,

Thanks, very much for u r help. It was not a problem with u r query. it was due to the data in the table.

Cheers

[bolastuff1975]

Further, to the given query. i am intrested in findign the records that have timestamp < ts3.
Assuming t5>t4>t31>t3>t2>t1.
i odnt want to update 103, 104 of KEY1.