Using Window Functions in an Update Statement

Using window functions in an update statement

The error is from postgres not django. You can rewrite this as:

WITH v_table_name AS
(
    SELECT row_number() over (partition by col2 order by col3) AS rn, primary_key
    FROM table_name
) 
UPDATE table_name set table_name.col1 = v_table_name.rn
FROM v_table_name
WHERE table_name.primary_key = v_table_name.primary_key;  

Or alternatively:

UPDATE table_name set table_name.col1 = v_table_name.rn
FROM  
(
    SELECT row_number() over (partition by col2 order by col3) AS rn, primary_key
    FROM table_name
) AS v_table_name
WHERE table_name.primary_key = v_table_name.primary_key;

This works. Just tested it on postgres-9.6. Here is the syntax for UPDATE (see the optional fromlist).

Hope this helps.

UPDATE column using a window function

You could use a common table expression to compute the new value, and then apply the new value to the original table, like:

with cte as (
    select      
        id, 
        time,
        first_value(id) over (partition by source order by time asc) as source
    from cat
)
update cat set cat.source = cte.source
from cte
where cte.id = cat.id and cte.time = cat.time

Update table with window function

The other possible solution (without CTE) is using UPDATE .. FROM syntax with subquery directly

UPDATE mr_usage outer
SET id = sub.new_id
FROM (
    SELECT 
        id, ROW_NUMBER() OVER (PARTITION BY uid, date(ts), title ORDER BY ts) AS new_id
    FROM
        mr_usage
) sub
WHERE outer.id = sub.id

But it is also available since PostgreSQL 8.4.

Create a 'partial' window function to update data in SQL Server

You can use first_value():

select
    id,
    coalesce(a, first_value(a) over (partition by id order by rnk)) a,
    coalesce(b, first_value(b) over (partition by id order by rnk)) b,
    coalesce(c, first_value(c) over (partition by id order by rnk)) c,
    rnk
from tbl;

Note that rank is a language keyword (as in window function rank() over()), hence not a good choice for a column name. I renamed it to rnk in the query.

Demo on DB Fiddle:

id |  a |    b |    c | rnk
-: | -: | ---: | ---: | --:
 1 | 10 |   10 | null |   1
 1 | 10 |   10 |   15 |   2
 2 | 10 | null | null |   1
 2 | 10 | null |   15 |   2
 2 | 10 | null |   15 |   3

Window functions in an update statement in Oracle SQL

You need parenthesis after ON as follows:

merge into TH USING
    (SELECT SEQUENCE, 
            ROW_NUMBER() OVER (PARTITION BY TRANSACTION_DATE, TRANSACTION_PARTNER 
                              ORDER BY TRANSACTION_DATE, TRANSACTION_PARTNER ASC) AS RN
    FROM TRANSACTION_HISTORY TH
    ) X
ON (X.SEQUENCE = TH.SEQUENCE) -- you need to use parenthesis
WHEN MATCHED THEN UPDATE 
SET TH.ID = X.RN

Using SQL window function to update values from next rows?

If you just want a query that returns your values, you can use:

select client,
       (case when min < lag(max) over (partition by client order by min)
             then lag(max) over (partition by client order by min) + 0.01
             else min
        end)
from t;

Notes:

• min and max are really bad choices for table names because they are SQL keywords.
• The 0.01 seems rather magical. I would suggest a different approach where the lower bound is inclusive and the upper bound exclusive. That way, you won't miss values such as 5.0005.
• If you want to update the table, the best method depends on the database you are using.

EDIT:

If you need to reset the max() as well, then use a subquery:

select t.*,
       (case when max < new_min then new_max = new_min + 0.01 else max end) as new_max
from (select t.*,
             (case when min < lag(max) over (partition by client order by min)
                   then lag(max) over (partition by client order by min) + 0.01
                   else min
              end) as new_min
      from t
     ) t

However, this might not work in complicated cases.

How to pipe window function output directly into a new window function in SQL?

Nesting of window functions is not allowed.

You need a subquery that returns MyMax and the main query to return MySum:

SELECT *, log10(SUM(MyMax) OVER SumFunct * 1000) * 10 AS MySum
FROM (
  SELECT *, Max(Watts) OVER (PARTITION BY Freq) AS MyMax
  FROM MyTable
)
WINDOW SumFunct as (PARTITION BY temp, Freq, channel);

Or, a CTE:

WITH cte AS (
  SELECT *, Max(Watts) OVER (PARTITION BY Freq) as MyMax
  FROM MyTable
)
SELECT *, log10(SUM(MyMax) OVER SumFunct * 1000) * 10 AS MySum
FROM cte
WINDOW SumFunct as (PARTITION BY temp, Freq, channel);

oracle sql update rows using analytical functions

Use the LEAD function and a correlated update.

UPDATE product p1
SET NEW_ORDER_DATE = (
        SELECT new_order_date
        FROM (
            SELECT PRODUCT_ID
                ,ORDER_DATE
                ,LEAD(ORDER_DATE, 1, DATE '2100-01-01' + 1) OVER (
                    PARTITION BY PRODUCT_ID ORDER BY ORDER_DATE
                    ) - 1 AS new_order_date
            FROM product
            ) p2
        WHERE p1.PRODUCT_ID = p2.product_id
            AND p1.ORDER_DATE = p2.ORDER_DATE
        );

Demo

---

Related Topics