Simplify SQL Query

[whojoedaddy]

Hi, I have a sql query, but it seems rather long. Is there an easier way to do this?

update HISTORY set HS_CHANGER = 'bes' where HS_CHANGE_DATE = (select MAX(HS_CHANGE_DATE) from HISTORY where HS_KEY = 138) and HS_CHANGE_TIME = (select MAX(HS_CHANGE_TIME) from HISTORY where HS_KEY = 138 and HS_CHANGE_DATE = (select MAX(HS_CHANGE_DATE) from HISTORY where HS_KEY = 138)) and HS_KEY = 138

Basically I'm trying to change the value of a column for a particular HS_KEY when it's the newest row that's been changed (latest date/time).

 

[PHV]

Why having TWO columns for a SINGLE DateTime value ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[whojoedaddy]

That's the way the database was created. Unfortunately I can't modify the table, I can only update/insert data into it.

 

[PHV]

You may try something like this:
update HISTORY
set HS_CHANGER = 'bes'
where HS_CHANGE_DATE || HS_CHANGE_TIME = (select MAX(HS_CHANGE_DATE || HS_CHANGE_TIME) from HISTORY where HS_KEY = 138)
and HS_KEY = 138

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886