Insert values to same table based on a computation

Transact-SQL
,
1

I have a table which i need to update inserting a value using a subtotal of it column.

CREATE TABLE [TEST].[dbo].[tb1]
([Ref] FLOAT, [SubTotal] INT)

INSERT INTO [TEST].[dbo].[tb1] VALUES
(1100,100),
(1100,200),
(1100,300),
(1300,200),
(1300,200),
(1400,500)

-- Subtotal

SELECT r1.[Ref], Sum(r1.[SubTotal]) as TotalAmount
FROM [TEST].[dbo].[tb1] r1
GROUP BY r1.[Ref]

How can i update the same table by inserting the result (TotalAmount) of above select query?

The Result i'm expecting is a table like this.

Ref SubTotal TotalAmount
1100 100 600
1100 200 600
1100 300 600
1300 200 400
1300 200 400
1400 500 500

Thanks you.

2

Is that a good idea? - i.e. it will be out-of-date as soon as a row is updated/inserted/deleted

usually:

Query it when you need it
Create a VIEW to do that if you need an easy & consistent way to get that data ...
... or add a computed column to do that
Or add a TRIGGER than updates the SubTotal whenever a row is updates/inserted/deleted (for JUST the matching [Ref] - but including both the Old and New [Ref] on UPDATE and IF the [Ref] changes)

3

Thanks for the reply and i agree with your point.

However this table is a test copy taken from another table.

And i want to join with another table using the TotalAmount.

So i think it is efficient if i have the TotalAmount cloumn in this table itself.

If i can add a new column with a TotalValues i think its a good idea.

4

Today's kludges become tomorrows permanent solutions :frowning:

If this is just a throw-away solution why not do it in the SELECT (e.g. that you are using to report on the data) rather than update the data in the table?

SELECT	r1.[Ref], r1.[SubTotal], R1A.TotalAmount
FROM	[TEST].[dbo].[tb1] AS r1
	JOIN
	(
		SELECT	r1.[Ref], Sum(r1.[SubTotal]) as TotalAmount
		FROM [TEST].[dbo].[tb1] AS r1
		GROUP BY r1.[Ref]
	) AS R1A
		ON R1A.[Ref] = R1.[Ref]
1 Like