Sp help to calculate field values 1

[barbola]

I am new to this and it's something I have to learn on the job. I have a table that gets data imported into it, but some of the fields need to be calculated.

We have a Python script that will import the raw data and I thought it would be better to have all the calcultions for the remaining fields in a SQL Stored Proc that gets run from the python script.

I thought I could use a simple Update statement but getting grief.

CREATE PROCEDURE .[SP_CalcStuff] AS 
UPDATE tblDetail  
	set Stuff= 
	(select 55.55-(0.555 * Field1) + (0.066 * Field2)
		+ (0.005 * Field1 * Field1) 
          from tblDetail where Stuff is null)
GO

The SP will be called for each record - not sure I can do something special in my SP for that other than assume there is only one null record.

I am getting the error:
[Red]
Server: Msg 512, Level 16, State 1, Procedure SP_CalcStuff, Line 3
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.[/Red]

 

[RiverGuy]

If you run just the part in parentheses in a query window, you will see that you have multiple records returned. So SQL Server will not know which record to use as an update. You need to redesign the query and use a join or correlated subquery if each record in tblDetail must have it's own unique value.

 

[barbola]

But I want each record updated with the value that is calculated. Why won't it do just that?

What would I join it to?

Each value in tblDetail (Field1 and Field2) have diff values so I do want a diff Stuff calculated. I do this in Access all the time with no problems.

 

[RiverGuy]

But I want each record updated with the value that is calculated. Why won't it do just that?

Because you are calculating multiple values.

Do you want every single record of your table to have the same exact value?

Going by what you are saying, my guess is no, you want each value updated differently. In that case, your query would look something like the following, if I understand it correctly

UPDATE tblDetail  
    set Stuff=
    55.55-(0.555 * Field1) + (0.066 * Field2)
        + (0.005 * Field1 * Field1)

 

[barbola]

Oh for crap sake. That just makes too much sense and is way too easy

I didn't need all that select fancy stuff.

thank you.

 

[RiverGuy]

Yeah, your query initially threw me off. Usually when you see a from clause in a subquery like that, you might be referencing a different table, which is why I was thinking you needed a join, before I took the time to reread your initial post.