Table update on multiple/join clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Table update on multiple/join clause

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-03 : 07:10:11

Hi, i need help please.
I am not good at updateing and always scared of messing up the data.

I need to update a table with the maximum id of a field in another table.

this works but only for one record where clause:
Update dbo.fcEventHist
set Eventhist = (select max(v5.ID) as max_id, from dbo.fcEvent)
where processid = 5144 and eventid = 95

But I want to update the entire table, i don't want to specify the processiD, as i want to update all with max id of processid with eventid = 95

Please Assist!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 07:17:14

what are fields by which two tables are related?

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-03 : 07:28:41

max id table = dbo.fcEventHist
update table = dbo.fcEventValue

dbo.fcEventHist fields (ProcessId, Id)
data: ProcessId Id
5144 20
5144 21

dbo.fcEventValue fields (EventhistId, CtrlVal)

They are linked: dbo.fcEventHist.Id = dbo.fcEventValue.EventhistId

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 09:10:08

is it this?

Update  eh
set eh.Eventhist = ev. max_value
from dbo.fcEventHist eh
inner join (select max(CtrlVal) as max_value,EventhistId from dbo.fcEventValue group by EventhistId)ev
on   eh.Id = ev.EventhistId

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-04 : 03:21:46

Great stuff - Thank You

Works 100% - updated accuratley

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 03:23:20

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources