Updating data from other rows within same table

[roody91]

I have a table that contains many records 6 mil today and may grow to over 20m when done.

I have a column called alarm_id which can be common to different rows of data. I have other columns called action_a and userid.

When a new record comes in with a unique alarm_id the action_a filed is 'new' and the userid is= 'new'

When a user acknowledges the alarm another record is generated with the same alarm_id but action_a ='ACK' and userid is set to the user who acknowledged the alarm e.g. 'abc'

What I want to do is set the userid of the action_a 'new' message with the userid of the action_a 'ACK' message for every unique/distinct alarm_id value.
Any help is appreciated.

Thanks,

Frank B.

 

[MeanGreen]

I think this will work:

update table
set user_id = b.user_id
from
table a join (
select alarm_id,user_id from table
where action_a = 'ACK'
group by alarm_id,user_id) b
on a.alarm_id = b.alarm_id


Good Luck.

 

[rac2]

Maybe -

UPDATE theTable
SET userid = ( SELECT DISTINCT userid
FROM theTable
WHERE action_a = 'ACK'
AND alarm_id = theTable.alarm_id
)
WHERE action_a = 'new'

 

[roody91]

Mean Green,

Thanks! your solution seems to be working except for one thing. I believe it updates all records no matter the action_a value(there are about 12 possible values). I would like to only update the records in which action_a = 'new'. I tried adding a where clase before the join but I get a syntax error. Any ideas?

Thanks again.

Frank B.

 

[MeanGreen]

Try This:

update table
set user_id = b.user_id
from
table a join (
select alarm_id,user_id from table
where action_a = 'ACK'
group by alarm_id,user_id) b
on a.alarm_id = b.alarm_id
where a.action_a = 'New'

Hope this helps.

 

[roody91]

Thanks again!

This works great.

I actually got it on my own before I read your last response. I had been placing the where clause in the wrong location in the message.

Thanks for you help