Update a record with highest date

Naweed · #1 (**permalink**) 07-01-09, 17:30

I am trying to select a record out of 40 with the highest date, to update. How should I do this.

col1 col2 col3 col3
ab1 xy2 lm3 date1
ab1 xy4 lm3 date1
ab1 xy5 lm3 date2 (higherst)
ab1 xy6 lm3 date1

I would like to selct the record for highest date2 and update lm3 to lm33.

How do I do this?

anacedent · #2 (**permalink**) 07-01-09, 20:31

Post DDL for table.
Post DML for test data.
Post expected/desired results (that matches test data).

Littlefoot · #3 (**permalink**) 07-02-09, 02:12

Something like

Code:

update your_table t1 set
  t1.col3 = lm33
where t1.col4 = (select max(t2.col4)
                 from your_table t2
                 where t2.col1 = t1.col1
                );

In other words: find the maximum COL4 date (possibly for every COL1 - thus the WHERE clause) and update this record's COL3 column to a new value.

Naweed · #4 (**permalink**) 07-09-09, 13:21

Thank you folks. I am sorry I wasnt able to respond sooner.

Internet Services

Web Development

Digital Marketing

Consumer Tech