hi all,

i have a table TABLE with 10 fields: INT1, DATE1, DATE2, etc etc.

INT1 DATE1 DATE2 ...
1 02-Jan-07 02-Oct-07 ...
2 02-Jan-07 02-Oct-07 ...
1 02-Feb-07 02-Oct-07 ...
1 20-Feb-07 10-Oct-07 ...

The user chooses which INT1 he likes to update(suppose he chooses 1) & then the DATE2 field corresponding to that INT1 is updated whose DATE1 field is latest(like here since user chose INT1=1, & DATE1 field of last row is latest(20-Feb-07), its DATE2 is updated from 10-Oct-07 to some other inputted date value say 15-Oct-07. How do i do that?

I was trying to do something like:

update
(select DATE2, max(DATE1) as dt from TABLE where INT1 = 1 group by DATE1)
set DATE2 = 15-Oct-07;

Can someone help forming a working query?
thank you

You need to first create query string dynamically
you can write you query in following manner, please take care of your front end syntax, here i m ignoring it

intvalue="1"
datevalue="20-feb-2008"
sSqltext="UPDATE tablename set DATE" & intvalue & "='" & datevalue "' WHERE INT1=" & intvalue

I also think there is some problem in database design. Any way I hope above query will help.

urt

Help yourself by helping someone.

well, there can be many rows with same INT1 field so the above query is not suitable. So, 1st getting the row with latest DATE1 field is required.

I did:

update
(select * from TABLE1 where DATE1 =
(select Max(DATE1) from TABLE1 where INT1 = 1)
)
set DATE2 = '15-Oct-08' ;

The result shows "1 row updated" but infact the updation takes place of Temporary table, not of the main table, TABLE1. The update is not reflected in TABLE1

How do i update the main table?

I am not able to understand your case

Try following syntax for updating table from table

update table1 set table1.col2=b.col2
from (select col1,col2 from table2) b
where table1.col1=b.col1

urt

Help yourself by helping someone.