Update Query needed to update the existing column

[agowtham2]

I need to replace one value of a column to another.

Example :

No Update_count Update_time
1 0 2015-02-02 12:23:24
1 0 2015-02-02 12:23:24
1 0 2015-02-02 12:24:25

For a particular No and update count the time should be same , in this case i need to set Max(update_time) as the time for all 3 records , Can someone help please.

Result should be like this :

No Update_count Update_time
1 0 2015-02-02 12:24:25
1 0 2015-02-02 12:24:25
1 0 2015-02-02 12:24:25

[Rabbit]

What problem are you having? Do you not know how to write an update query? Or do you not know how to write a query to retrieve the max value? Or do you not know how to combine the two?

[agowtham2]

Hi ,

There are lot of numbers in the table , i need to place the max update_time if the update count is same .

For a particular update time and No the update time should be same , if its different then i need to replace it with the Max(update_time) for that update count.

I hope u got my question

[Rabbit]

I understand the question. I don't understand which part of the question you're having trouble with. Please answer my original questions.

[agowtham2]

Hi ,

I have retrived the maximum value for each combination of update_count and No.

I don't know how to write an update statement that updates the proper value based on the key combination of Update_count and no.

[Rabbit]

Ok, so you the problem you are having is that you don't know how to combine the two queries?

Here's an example

UPDATE t
SET fieldName = x.otherFieldName
FROM
   table1 AS t
 
   INNER JOIN ( ... max query ... ) AS x
   ON t.keyField = x.keyField

[agowtham2]

update Xipu.tqx0sn_Change_audit_day as t
set t.Update_time = X.Update_time 
X X X  
select * from (select  Max(A.Update_time) as Update_time , A.Task_number , A.Update  from Xipu.tqx0sn_Change_audit_day A 
join Xipu.tqx0sn_Change_audit_day B 
On A.Task_number = B.Task_number  
And A.Update = B.Update
And A.Update_time <> B.Update_time
group by A.Update , A.Task_number ) X
on t.task_number = X.Task_number
and t.Update = X.Update

This is the query i wrote , how to join both . can u please help.

[Rabbit]

Please use code tags when posting code or formatted data.

Use my example as a template on how to combine your queries.

[agowtham2]

I a working in DB2 and i tried ur example and tried and it was not working. can u please edit the query i sent and post it

[Rabbit]

Please post your attempt at combining the queries.

[agowtham2]

update Xipu.tqx0sn_Change_audit_day as t
    set t.Update_time = X.Update_time 
    X X X  
    select * from (select  Max(A.Update_time) as Update_time , A.Task_number , A.Update  from Xipu.tqx0sn_Change_audit_day A 
    join Xipu.tqx0sn_Change_audit_day B 
    On A.Task_number = B.Task_number  
    And A.Update = B.Update
    And A.Update_time <> B.Update_time
    group by A.Update , A.Task_number ) X
    on t.task_number = X.Task_number
    and t.Update = X.Update
 

This is the query i used . in the place of XXX i used 'from' , 'Innerjoin' . none is working

[Rabbit]

Please use code tags when posting code or formatted data.

Here's what I see wrong in your code:
1) I don't put a table name in the first line. It's only the alias.

2) You didn't subquery your entire query that you use to get the max. In fact, there's no need for you to do an additional select * on your subquery, it is unnecessary.

3) You do an additional join in your subquery that is unnecessary

Try this

update t
set Update_time = X.Update_time 
from 
    Xipu.tqx0sn_Change_audit_day as t
 
    inner join (
        select Max(A.Update_time) as Update_time, A.Task_number, A.Update 
        from Xipu.tqx0sn_Change_audit_day
        group by A.Update , A.Task_number
    ) X on
        t.task_number = X.Task_number and
        t.Update = X.Update