CODEWITHSUNDEEP
sqlsql-serversubquery
hakubaa
hakubaa

Reputation: 169

Update table with subquery

I hava the following table in database (Access - Microsoft SQL Server):

| Product Id |  Month    | Sales |
----------------------------------
| 1144       |  1        | 100   |
| 20131120   |  1        | 200   |
| 1144       |  2        | 333   |
| 1144       |  3        | 333   |
| 1144       |  4        | 333   |
| 1144       |  5        | 333   |
| 20131120   |  2        | 200   |

And I would like to add to the table new column which will show in how many months each products has been sold till particular month. I need to keep this information in database, in this table.

After updating table I would like to get the table:

| Product Id |  Month    | Sales | Counter |
-------------------------------------------|
| 1144       |  1        | 100   |   0     |
| 20131120   |  1        | 200   |   0     |
| 1144       |  2        | 333   |   1     |
| 1144       |  3        | 333   |   2     |
| 1144       |  4        | 333   |   3     |
| 1144       |  5        | 333   |   4     |
| 20131120   |  2        | 200   |   1     |

For example, for product=1144 and month=3, counter=2 because this item has appeared twice till 3 month.

I would like to update column Counter with one query (update set = (select ... )). Could you help me to construct the query ?

Upvotes: 0

Views: 146

Answers (3)

StuartLC
StuartLC

Reputation: 107237

In SqlServer, you can project your column ranking directly using row_number()

select 
    productid, 
    [month], 
    sales, 
    row_number() over (partition by productid order by [month]) - 1 as [Rank]
from mysales

Assuming the columns [ProductId, MonthId] constitute a key, you can persist this:

-- Create new column
ALTER TABLE mysales ADD [Rank] INT
GO

-- Set the new Column
UPDATE m 
    SET m.[Rank] = X.[Rank]
    FROM mysales m
    INNER JOIN
       (
         SELECT 
            ProductId, 
            Month, 
            row_number() over (partition by productid order by [month]) - 1 AS [Rank]
         FROM MySales
        ) X
    ON m.ProductId = X.ProductID AND m.[Month] = X.[Month];

Fiddle here

Upvotes: 2

MauriDev
MauriDev

Reputation: 445

This query should retrive the correct data:

SELECT    m1.product_id, m1.month, m1.sales, COUNT(m2.month) - 1 AS counter
  FROM    mysales AS m1 INNER JOIN
          mysales AS m2 ON m1.product_id = m2.product_id AND m1.month >= m2.month
 GROUP BY m1.product_id, m1.month, m1.sales

so the statment to update the table once you added the counter column is:

UPDATE    mysales
   SET    counter = x.counter
  FROM    (SELECT    m1.product_id, m1.month, COUNT(m2.month) - 1 AS counter
             FROM    mysales AS m1 INNER JOIN
                     mysales AS m2 ON m1.product_id = m2.product_id AND m1.month >= m2.month
            GROUP BY m1.product_id, m1.month) AS x INNER JOIN
          mysales ON x.product_id = mysales.product_id AND x.month = mysales.month

This syntax for the update statement works on SqlServer, I don't know if it works also on MS Access.

Upvotes: 0

LcSalazar
LcSalazar

Reputation: 16821

If i understood it correctly, the months will always be sequential, right? Then just update the Counter column to (Month - 1).

UPDATE [table] SET [Counter] = [Month] - 1

Upvotes: 0

Related Questions