Update Qry - update where max record returned? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Update Qry - update where max record returned?

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2007-12-31 : 03:53:41

Hi, I need help please!

I have multiple instances of a field, when i save it creates a copy in table. So i want to update the max (latest one) which is only one record.

This is the code i use to return the max value which works:SELECT MAX(fcEventValue.ControlID) as test FROM fcEventHist INNER JOIN fcEventValue ON dbo.fcEventHist.ID = fcEventValue.EventHistID WHERE (fcEventHist.ProcessID = 594) AND (fcEventValue.ControlID = 'FG9030Start')

I want to update it now, tried the following but keep on updating multiple records instaed of only the max or last one:
Does not work!
UPDATE fcEventValue SET ControlValue = 'True' where ControlID = (SELECT MAX(fcEventValue.ControlID) as test FROM fcEventHist INNER JOIN fcEventValue ON dbo.fcEventHist.ID = fcEventValue.EventHistID WHERE (fcEventHist.ProcessID = 594) AND (fcEventValue.ControlID = 'FG9030Start'))

Please Help!

Regards,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 04:05:03

[code]UPDATE t1
SET t1.ControlValue = 'True'
FROM fcEventValue t1
INNER JOIN (
SELECT MAX(fcEventValue.ControlID) as test
FROM fcEventHist
INNER JOIN fcEventValue
ON dbo.fcEventHist.ID = fcEventValue.EventHistID
WHERE (fcEventHist.ProcessID = 594)
AND (fcEventValue.ControlID = 'FG9030Start')) t2
ON t2.test = t1.ControlID[/code]

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2007-12-31 : 04:23:17

Hi, Thanks for the help!

Unfortunately, It updates no record!

The Select returns one record: (i changed field to controlvalue)
SELECT MAX(fcEventValue.ControlValue) as test
FROM fcEventHist
INNER JOIN fcEventValue
ON dbo.fcEventHist.ID = fcEventValue.EventHistID
WHERE (fcEventHist.ProcessID = 594)
AND (fcEventValue.ControlID = 'FG9030Start')

But it does not update: 0 rows affected
UPDATE t1
SET t1.ControlValue = 'True'
FROM fcEventValue t1
INNER JOIN (
SELECT MAX(fcEventValue.ControlValue) as test
FROM fcEventHist
INNER JOIN fcEventValue
ON dbo.fcEventHist.ID = fcEventValue.EventHistID
WHERE (fcEventHist.ProcessID = 594)
AND (fcEventValue.ControlID = 'FG9030Start')) t2
ON t2.test = t1.ControlID

Please Assist!

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 04:28:35

change it here also:-

UPDATE t1 
SET t1.ControlValue = 'True' 
FROM fcEventValue t1
INNER JOIN (
SELECT MAX(fcEventValue.ControlValue) as test 
FROM fcEventHist 
INNER JOIN fcEventValue 
ON dbo.fcEventHist.ID = fcEventValue.EventHistID 
WHERE (fcEventHist.ProcessID = 594) 
AND (fcEventValue.ControlID = 'FG9030Start')) t2
ON t2.test = t1.ControlValue

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2007-12-31 : 04:35:43

Changed it, but now it updates 17 rows (multiple rows) intead of only the one (which the select returns)!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 04:44:36

Try this:-
UPDATE t1
SET t1.ControlValue = 'True'
FROM fcEventValue t1
INNER JOIN fcEventHist h
ON h.ID = t1.EventHistID
INNER JOIN (
SELECT MAX(fcEventValue.ControlValue) as test
FROM fcEventHist
INNER JOIN fcEventValue
ON dbo.fcEventHist.ID = fcEventValue.EventHistID
WHERE (fcEventHist.ProcessID = 594)
AND (fcEventValue.ControlID = 'FG9030Start')) t2
ON t2.test = t1.ControlValue
WHERE (h.ProcessID = 594)
AND (t1.ControlID = 'FG9030Start')

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2007-12-31 : 05:03:40

Great!!! Thank You it works - i just had to fix my select first as it was not retrieving the max one.

It now updates the correct one, due to your kindness & great help!

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 05:06:55

Glad that i could help you to solve this. Please come back whenever you have to solve any problem. Cheers.

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2007-12-31 : 05:11:22

The final & complete code:

UPDATE t1
SET t1.ControlValue = 'False'
FROM fcEventValue t1
INNER JOIN fcEventHist h
ON h.ID = t1.EventHistID
INNER JOIN (
select MAX(v.ControlValue) as test
from dbo.fcEventHist as e
inner join dbo.fcEventValue as v
on e.ID = v.EventHistID
and v.ControlID = 'FG9030Start'
join (select max(v1.EventHistID) as max_id,v1.ControlID from dbo.fcEventHist E1
left join dbo.fcEventValue V1 on E1.ID = V1.EventHistID where E1.ProcessID =595 group by v1.controlid) max_qry
on max_id = V.EventHistID and max_qry.ControlID = V.ControlID
where e.ProcessID =595) t2
ON t2.test = t1.ControlValue
WHERE (h.ProcessID = 595)
AND (t1.ControlID = 'FG9030Start')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources