1

I want to update column price in table but I can't.

Sutract price id = 1304  and n = 1  and price with  id = 1304 and n= 2 

(price with id =1307 and n = 1 ) - (price with  id = 1307 and n= 2) 
and 
(price id =1307 and n = 1 - price with id = 1307 and n= 3) 
and 
(price id = 1307 and n = 1 - price with id = 1307 and n= 4) 
and
(price id = 1307 and n = 1 - price with id = 1307 and n= 5)

TBL_Price now :

    |     id    |N |      Price    | 
--------------------------------------------------------------------------
    |      1304 | 1|         32000 |
    |      1304 | 2|         28000 |
    |      1305 | 1|         42000 |
    |      1306 | 1|         52000 |
    |      1306 | 2|         38000 |
    |      1306 | 3|         63000 |
    |      1306 | 4|         52000 |
    |      1307 | 1|         15000 |
    |      1307 | 2|         12000 |
    |      1307 | 3|         15000 |
    |      1307 | 4|         17000 |
    |      1307 | 5|         20000 |

The result is:

    |     id    |N |      Price    | 
--------------------------------------------------------------------------
    |      1304 | 1|         32000 |
    |      1304 | 2|         -4000 |
    |      1305 | 1|         42000 |
    |      1306 | 1|         52000 |
    |      1306 | 2|        -14000 |
    |      1306 | 3|        +11000 |
    |      1306 | 4|         0     |
    |      1307 | 1|         15000 |
    |      1307 | 2|         -3000 |
    |      1307 | 3|         0     |
    |      1307 | 4|         +2000 |
    |      1307 | 5|         +5000 | 

try:

SELECT [ID]
    ,[N]
     ,[Price]
  ,coalesce((select Price - Price where ID = ID and N = 2),price)
 FROM [dbo].[Tbl_Price]
4
  • 1
    Where is row field?
    – McNets
    Feb 21, 2017 at 11:04
  • ? row field? no Row = N
    – RedArmy
    Feb 21, 2017 at 11:07
  • Which version of SQL Server? Feb 21, 2017 at 11:17
  • 2014 sp1 or sp2
    – RedArmy
    Feb 21, 2017 at 11:18

2 Answers 2

1

OK, so let's rephrase:

for each id, if N=1, return price, else, return price for N=1 for the same id minus current price.

You can try this ugly:

select *, iif(tt.N = 1, t.price, isnull(tt.price, 0) - t.price)
from my_table t
left join (select * from my_table) tt on t.id = tt.id
where t.N = 1

Or a trendier version using first_value statement (starting from version 2012) and assuming we have, for all id, a row with N=1:

select *, iif(N=1,price, price - first_value(price) over(partition by id order by N) )
from my_table 
4
  • thanks @irimias , Is it possible for me to explain?
    – RedArmy
    Feb 21, 2017 at 11:13
  • @McNets Yes, iif() was added in 2012. Feb 21, 2017 at 11:25
  • @ypercubeᵀᴹ ok, I always associate it with mysql.
    – McNets
    Feb 21, 2017 at 11:27
  • ? explain this code?
    – RedArmy
    Feb 21, 2017 at 11:30
1

You need to modify prices of rows where N > 1, using price where N = 1:

UPDATE  tp1
SET     Price = Price - tp2.Price
FROM    TBL_Price tp1
        INNER JOIN TBL_Price tp2 ON tp2.id = tp1.id AND tp2.N = 1
WHERE   tp1.N > 1

OR

UPDATE  tp1
SET     Price = Price - (SELECT Price FROM TBL_Price WHERE id = tp1.id and N = 1)
FROM    TBL_Price tp1
WHERE   N > 1

If you want to check it before to update:

SELECT  tp1.id, tp1.N, tp1.Price = tp1.Price - tp2.Price AS NewPrice
FROM    TBL_Price tp1
        INNER JOIN TBL_Price tp2 ON tp2.id = tp1.id AND tp2.N = 1
WHERE   tp1.N > 1
1
  • can u select before update? edit for select but no result
    – RedArmy
    Feb 21, 2017 at 11:29

Not the answer you're looking for? Browse other questions tagged or ask your own question.