0

I am new to SQL and not getting even simple things. I am wondering if the following code does not add a real column?

select * 
sum(value) over (partition by prename order by NumberOfDays) SumValue
from (select *, row_number() over (order by NumberOfDays) rn from 
tablenames) as t
order by rn 

I thought with this code I would create a new column called SumValue. When I execute the code above it works fine and the column SumValue with all its values is shown but when I try to calcualte with this column, let's say:

ALTER TABLE tablenames
ADD AvValue float; 
UPDATE [DatabaseXY].[dbo].[tablenames]
SET AvH = [SumValue]/[NumberOfDays]

SSMS tells me that SumValue is an invalid column name. Why is that? Even if I refresh or restart SSMS and look into the object explorer there is no column called SumValue.. I guess with my presented code I only create kinda temporary columns but how do I add those values to a real column?

Thank you so much!

1 Answer 1

1

No column is added to the table during the select. Adding columns to a table is usually restricted to a lot fewer people, than those that are allowed to select from a table. You can do this as:

ALTER TABLE tablenames
    ADD AvValue float;

MERGE INTO tablenames x
USING (
    select t.* 
         , sum(value) over (partition by prename order by NumberOfDays) SumValue
    from tablenames t
) y
    ON x.<key> = y.<key>
WHEN MATCHED THEN
    UPDATE SET x.AvValue = y.SumValue/y.NumberOfDays

All untested since I don't know what the key for tablenames is.

You can probably calculate AvValue directly in the USING clause, but I'll leave that as an exercise.

EDIT: The implementation of MERGE in SQL-server seems to have several problems, see use-caution-with-sql-servers-merge-statement/ (link provided by Aaron Bertrand in comment. Using an update instead would be something like (still untested):

WITH y AS (
    SELECT t.* 
     , SUM(value) OVER (PARTITION BY prename 
                        ORDER BY NumberOfDays) SumValue
    FROM tablenames t
)
UPDATE y
SET y.AvValue = y.SumValue/y.NumberOfDays;
6
  • Just please be very cautious with MERGE, especially when you don't really get any benefit from using it (this is an UPDATE and nothing more). Sep 8, 2019 at 16:17
  • Phew, quite impressive list of bugs. I usually dont use SQL server, so I had no idea. Thanks for pointing that out Sep 8, 2019 at 16:21
  • 1
    @Lennart, MERGE is hard for DBMS developers. Now you know why PostgreSQL hasn't implemented it even in the latest version.
    – Dan Guzman
    Sep 8, 2019 at 17:07
  • I'm not convinced that introducing an extension to compensate for the lack of a standard construction is the way to go though. Another open source alternative has a history of just that... ;-) Sep 8, 2019 at 17:26
  • Thank you so much! As I am pretty new to SQL I am a bit confused now. So I better dont use MERGE but rather UPDATE? How should I implement it? Sorry for my dumb question..
    – blowbuh
    Sep 8, 2019 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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