Update column based on criteria in row

1

As a beginner I was wondering if someone could direct me to a tutorial or give me a generic query to achieve the following

I want to update a column in a record (i.e same row) where a total from another column is calculated based on two criteria in the record(row).

Hoping my question is clear..

Regards

2 
;WITH cte AS
(
	RowId,
	ComputationResult = SUM(YourComputationFormula)
	FROM 
		YourTable
	WHERE
		CriteriaColumn1 = 'something'
		AND CriteriaColumn2 = 'somethingelse'
	GROUP BY
		RowId
)
UPDATE y SET 
	AColumn = ComputationResult
FROM
	YourTable y
	INNER JOIN cte c ON
		c.RowId = y.RowId;
3

Thank you James. Herein lies my problem.

The criteria for both columns are already in different columns in the same row.

So 'something' and 'somethingelse' are variables in the same record (row)

Regards

4

How are you accessing the specific record? If the record has a key, you can do it by addressing the "field" in the row that JamesK was alluding to. Rows go across and columns are vertical. Where they intersect is a field. JamesK's CriteriaColumn1 represents the first criteria you want to check to see if the field gets updated. Ditto for the second check.
The "key" to the record is represented in JamesK's code by the RowId, but that would be the name of whatever key or key combination that identifies the record you are checking for update.

5

Thank you guys for your replies. As an complete newbie I do not follow your answer. I've tried different queries,to no avial. Please be patient with me. I think best to explain myself is with to images. Hope it helps to explain myself.

Before the update of the table (called Data) the table looks like this

After the update the table would look like this

The last column (gem) is calculated by sum in the ton column divide by the sum of the opp column where the kult and klas in the two columns are the same as for the specific row.

The following excel formula does the trick =(SUMIFS(ton,kult,D3,klas,E3))/(SUMIFS(opp,kult,D3,klas,E3))
where D3 is the kult field in the row and E4 is the klas field in the row.

I hope referring to Excel is not too simple, but I do not know how to express myself better.

Thank you and regards.

6 
;with cte as
(
	select
		gem,
		res = 
			sum(ton) over(partition by kult,klas)/
			nullif( sum(opp) over ( partition by kult,klas), 0)
	from
		YourTable
)
update cte set gem = res;
1 Like
7

Thank you JamesK worked like a charm.