Update Loop through Data - SQL Server Forums

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

SQL Server Development (2000)

Update Loop through Data

Author

Topic

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-10-11 : 09:43:02

I know I start off every topic with this, but I am new to SQL (3.7 weeks in now).

I have an update statement that I am putting in a stored proc and will setup a job to run once a month.

UPDATE    emp_time_table
SET              time_accrued =
                          (SELECT     time_off.time_off_accrual_rate + emp_time_table_1.time_accrued AS new_time_accrued
                            FROM          time_off INNER JOIN
                                                   emp_time_table AS emp_time_table_1 ON time_off.time_off_id = emp_time_table_1.to_type_id),
last_updated = GETDATE()

The problem I am having is that I need this to run through the entire table and do this. It works fine when I have one row in my table, but when I added a second row, I got the following error:

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.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-11 : 09:51:25

Write it like a regular query, when you make a column equal to a subquery, the subquery has to return a single value which gets assigned to that column. For what you want, think of it as a regular SELECT query, that is being told to update one of the tables - note that you can always be absolutely certain that only one table is being updated, so don't worry that you might update one of the other tables by mistake. The one being updated is the one named by the UPDATE statement. Then you have a regular FROM (and WHERE if appropriate) and the rows in the table being updated that would be returned if this was just a SELECT are the ones that are updated. So your query should be

UPDATE emp_time_table
SET time_accrued = time_off.time_off_accrual_rate + emp_time_table_1.time_accrued, last_updated = GETDATE()
FROM time_off 
INNER JOIN emp_time_table AS emp_time_table_1 ON time_off.time_off_id = emp_time_table_1.to_type_id

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-10-11 : 10:09:52

Awesome, thank you. Makes sense now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources