CODEWITHSUNDEEP
sqlsql-update
Devilsphere
Devilsphere

Reputation: 3

SQL UPDATE field using value from another row with criteria

I have a table

ItemPriceId
Version
PricingSchemeID
UnitPrice
LastModUser
LastModDttm
TimeStamp
ProdId

This table contains multiple pricing schemes for approximately 1300 items.
What I am trying to do is alter one of these schemes to be a multiple of another scheme.
So if the PricingSchemeID = 102
I want to update each UnitPrice to be equal to PricingSchemeID = 100's (UnitPrice*1.40) for each ProdId

Here is sample data: 

ItemPriceId PricingSchemeID UnitPrice ProdId   
    1             100         10.25     1  
    2             100         18.52     2  
    3             100         12.25     3  
    4             100         19.00     4  
    5             100         21.00     5  
    6             101         16.62     1  
    7             101         15.84     2  
    8             101         12.95     3  
    9             101          8.25     4  
    10            101          6.50     5  
    11            102         63.95     1  
    12            102         44.25     2 
    13            102         75.26     3 
    14            102         96.25     4 
    15            102         56.58     5 
    16            103         12.25     1 
    17            103         13.25     2 
    18            103         14.25     3 
    19            103         15.25     4 
    20            103         16.25     5 
    21            104         25.35     1 
    22            104         53.26     2 
    23            104         15.65     3 
    24            104         43.65     4 
    25            104         96.35     5

So it should do this:

ItemPriceId PricingSchemeID UnitPrice ProdId   
    11            102         14.35     1  
    12            102        25.928     2  
    13            102         17.15     3  
    14            102         26.60     4  
    15            102         29.40     5

Thanks for any help in advance.

Upvotes: 0

Views: 56

Answers (1)

SQLChao
SQLChao

Reputation: 7847

Here's a select statement that represents what you are after which joins the table on itself.

SELECT yt1.itempriceid
    , yt1.pricingschemeid
    , yt2.unitprice * 1.4
    , yt1.prodid
FROM yourTable yt1
    JOIN yourTable yt2 ON yt1.prodid = yt2.prodid
WHERE yt1.pricingschemeid = 102 
    AND yt2.pricingschemeid = 100

Here's the same thing as an update.

UPDATE yt1
SET yt1.unitprice = yt2.unitprice * '1.4'
FROM yourTable yt1
    JOIN yourTable yt2 ON yt1.prodid = yt2.prodid
WHERE yt1.pricingschemeid = 102 
    AND yt2.pricingschemeid = 100

Upvotes: 1

Related Questions