CODEWITHSUNDEEP
sqlsql-server
Rui Braga
Rui Braga

Reputation: 1

Update table using subquery

I have a Foo table with the following attributes:

FooID (PK) | Bar | Price

Given that a sample record set is:

1 | 332 | 10
2 | 332 | 10
3 | 333 | 5
4 | 334 | 30
4 | 334 | 30
4 | 334 | 30

I want the output of the query to be

1 | 332 | 5
2 | 332 | 5
3 | 333 | 5
4 | 334 | 10
4 | 334 | 10
4 | 334 | 10

In other words, the Price column, should be the result of: Price/N where N is the number of times that the given Bar exists in the record set.

I have managed to count the number of Bars that exist using:

  select Bar, count(*) as BarCount from Foo GROUP BY Bar

However, I have no clue how I am going to apply this to the division of the Price.

Thanks!

Upvotes: 0

Views: 45

Answers (3)

GMB
GMB

Reputation: 222432

Use a correlated subquery to compute the divider :

WITH myTable AS (
    SELECT 1 AS FooID, 332 AS Bar, 10 AS Price UNION ALL
    SELECT 2, 332, 10 UNION ALL
    SELECT 3, 333, 5  UNION ALL
    SELECT 4, 334, 30 UNION ALL
    SELECT 4, 334, 30 UNION ALL
    SELECT 4, 334, 30
)    
SELECT 
    t.fooid,
    t.bar,
    t.price / (SELECT COUNT(*) FROM myTable WHERE bar = t.bar)
FROM myTable t
GO

fooid | bar | (No column name)
----: | --: | ---------------:
    1 | 332 |                5
    2 | 332 |                5
    3 | 333 |                5
    4 | 334 |               10
    4 | 334 |               10
    4 | 334 |               10

db<>fiddle here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269553

You can use window functions and division:

select fooid, bar, 
       price / count(*) over (partition by bar) as new_price
from t;

Demo

Note that SQL Server does integer division. So if price is an integer, you might get a more accurate result with:

select fooid, bar, 
       price * 1.0 / count(*) over (partition by bar) as new_price
from t;

EDIT:

The title says "update", but the question only mentions getting results. If you want an update use an updatable CTE or subquery:

with toupdate as (
      select t.*, 
             price / count(*) over (partition by bar) as new_price
      from t
     )
update toupdate
    set new_price = price
    where price <> new_price;

Upvotes: 4

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520958

You could use a CTE which finds the counts for each Bar group, and then join to it to find the quotients:

WITH cte AS (
    SELECT Bar, COUNT(*) AS cnt
    FROM yourTable
    GROUP BY Bar
)

SELECT t1.FooID, t1.Bar,
   1.0 * t1.Price / t2.cnt AS NewPrice
FROM yourTable t1
INNER JOIN cte t2
    ON t1.Bar = t2.Bar
ORDER BY
    t1.Bar, t1.FooID;

enter image description here

Demo

Gordon's answer is probably the fastest, because it finds the counts and computes the quotients in one single pass over the entire table. The answer I gave requires an additional subquery and join.

Upvotes: 2

Related Questions