Updating Table based on Max results of other table

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)

Updating Table based on Max results of other table

Author

Topic

Darrenmf
Starting Member

5 Posts

Posted - 2010-02-01 : 06:30:08

I am currently trying to run an update and getting the error message - Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

I am assuming this means i have to do 1 to 1 updates? Is there any way i can do a bulk update?

The code i am using at current is:

UPDATE Table1
SET Table1.Column1 = (SELECT max(Table2.Column1)
FROM Table2 ,Table1 WHERE Table1.Column2 = table2.column2
group by Table1.Column2,Table1.Column1
having Table1.Column1 <> max(Table2.Column1))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 06:36:40

this means the subquery returns more than one value so you might need to join to subquery to do the update

Darrenmf
Starting Member

5 Posts

Posted - 2010-02-01 : 06:38:51

Right ok, Can you define how within the query this would be done?

I have played with joins unsuccessfully on this query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 06:48:55

something like:-


UPDATE t1
SET	 t1.Column1 = t2.MaxVal
FROM Table1 t1
JOIN (SELECT Table2.Column2,max(Table2.Column1) AS MaxVal
FROM Table2 
group by Table2.Column2
) t2
ON  t1.Column2 = t2.column2
WHERE t1.Column1 <> t2.MaxVal

Darrenmf
Starting Member

5 Posts

Posted - 2010-02-01 : 06:55:22

That is genius.....Much appreciated..Worked blindingly

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-01 : 06:59:05

Better you do it as SELECT statement as you dont need to run UPDATEs whenever data are added

Madhivanan

Failing to plan is Planning to fail

Darrenmf
Starting Member

5 Posts

Posted - 2010-02-01 : 07:01:34

This was to fix a defect as the data is automatically populated based on a trigger when one column changes in one table it should update value into the other.. We had some rows where this had not occurred so this is a one of update to bring both tables into line with one another, So a select statement would not have helped. Thanks anyways

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 05:15:46

quote:
Originally posted by Darrenmf

That is genius.....Much appreciated..Worked blindingly

welcome

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 05:16:34

quote:
Originally posted by Darrenmf

This was to fix a defect as the data is automatically populated based on a trigger when one column changes in one table it should update value into the other.. We had some rows where this had not occurred so this is a one of update to bring both tables into line with one another, So a select statement would not have helped. Thanks anyways

ok great. so is this logic now included in trigger to do update automatically in future?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources