Update ALL Rows in one pass 3

[FailSafe]

I need to be able update all the rows in Table using information from another Table and a Formula.

EG:

Table #1 (Only has ONE Record)
GoodMails (Int)
BadMails (Int)

Table #2
Tokens (String)
GoodTokens (Int)
BadTokens (Int)
Score (Numeric(18,4)

I only want to read Table 1 ONCE
I then need to Update ALL the Records in Table 2 with a calculation based on the numeric data.

PSEUDO CODE:
Table2.Score = (Table2.BadTokens / Table1.BadMails) / ((Table2.BadTokens / Table1.BadMails) + (Table2.GoodTokens / Table1.GoodMails))

I need to do this with a Stored Procedure with no Parameters passed. Can anyone help?


Brian Gillham
FailSafe Systems

 

[dmcmunn]

Update a
set a.Score
= (a.BadTokens / b.BadMails)
/ ( (a.BadTokens / b.BadMails)
+ (a.GoodTokens / b.GoodMails))
from Table2 a
cross join Table1 b

 

[ttba]

Another option...

DECLARE @GoodMails INT, @BadMails INT

SELECT @GoodMails = GoodMails, @BadMails = BadMails
FROM Table1

UPDATE Table2
SET Score = (Table2.BadTokens / @BadMails) / ((Table2.BadTokens / @BadMails) + (Table2.GoodTokens / @GoodMails))

 

[SQLSister]

Unless you want all your division totals rounded to the nearest integer, you will need to convert those values to decimal before doing the division.

Questions about posting. See faq183-874

 

[FailSafe]

Is the Conversion correct here or is there a simpler way.
CREATE PROC spUpdateTokenScore AS
SET NOCOUNT ON -- Suppress record count messages for a performance boost.
-- Declare two variables.
DECLARE @HAMS Numeric(18,4), @SPAMS Numeric(18,4)

-- Populate the variables with values from the Params Table
SELECT @HAMS = dbo.Params.BayeHam, @SPAMS = dbo.Params.BayeSpam FROM dbo.Params WHERE Config = 0

-- Perform the update
IF @HAMS > 0 AND @SPAMS > 0
UPDATE dbo.Tokens
SET dbo.Tokens.Score = (CONVERT(Numeric(18,4),dbo.Tokens.Spams) / @SPAMS) / ((CONVERT(Numeric(18,4),dbo.Tokens.Spams) / @SPAMS)+(CONVERT(Numeric(18,4),dbo.Tokens.Hams) / @HAMS))
GO


Brian Gillham
FailSafe Systems

 

[SQLSister]

Would that there were a simpler way, alas there is not.

Questions about posting. See faq183-874