Update Table based on Calculated Column

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)

Update Table based on Calculated Column

Author

Topic

kellog1
Starting Member

35 Posts

Posted - 2010-06-21 : 18:03:57

Here is my scenario...I have Table A (ColA int null, ColB int null) and Table B (Col1...Col12). I want to update ColA from Table A when ColA is null and if it is null then populate is based on Calculations from Table B.

Here is my select statement for doing Calculation from Table B...

SELECT s.col1,
CAST(SUM(ROUND(s.col2, 2, 1) - ROUND(s.col3, 2, 1)) AS numeric(5,2)) As Length
FROM TableB s

How do I update ColA from Table A based on the calculation from TableB?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-06-21 : 18:37:47

The basic pattern is:[CODE]
update a
set ColA = s.Col1
from TableA a
inner join
TableB s
on a.Key = b.Key
where a.ColA is NULL[/CODE]
You didn't mention how tables A and B relate to each other. Supply that logic in the join condition and you should be ready to test the results.

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

kellog1
Starting Member

35 Posts

Posted - 2010-06-21 : 20:23:35

Here is the error I am getting...

Msg 512, Level 16, State 1, Line 2
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.

SQL Statement:

UPDATE DimProjectPublication
SET OverrideProjectLengthMiles =
(select CAST(SUM(ROUND(s.EndMile, 2, 1) - ROUND(s.BeginMile, 2, 1)) AS numeric(5,2)) CalcLength
from DimProjectStation s
join DimProjectPublication b
on s.PublicationId = b.PublicationId
and s.ProjectPublicationId = b.ProjectPublicationId
group by s.ProjectPublicationId)
where OverrideProjectLengthMiles is null

quote:
Originally posted by Bustaz Kool

The basic pattern is:[CODE]
update a
set ColA = s.Col1
from TableA a
inner join
TableB s
on a.Key = b.Key
where a.ColA is NULL[/CODE]
You didn't mention how tables A and B relate to each other. Supply that logic in the join condition and you should be ready to test the results.

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources