SQL UPDATE in a SELECT rank over Partition sentence
14,694
Solution 1
You could join the sub-query and do an UPDATE:
UPDATE table_name t2
SET t2.rank=
SELECT t1.rank FROM(
SELECT company,
direction,
type,
YEAR,
MONTH,
value,
rank() OVER (PARTITION BY direction, type, YEAR, MONTH ORDER BY value DESC) AS rank
FROM table_name
GROUP BY company,
direction,
TYPE,
YEAR,
MONTH,
VALUE
ORDER BY company,
direction,
TYPE,
YEAR,
MONTH,
VALUE
) t1
WHERE t1.company = t2.company
AND t1.direction = t2.direction;
Add required conditions to the predicate.
Or,
You could use MERGE and keep that query in the USING clause:
MERGE INTO table_name t USING
(SELECT company,
direction,
TYPE,
YEAR,
MONTH,
VALUE,
rank() OVER (PARTITION BY direction, TYPE, YEAR, MONTH ORDER BY VALUE DESC) AS rank
FROM table1
GROUP BY company,
direction,
TYPE,
YEAR,
MONTH,
VALUE
ORDER BY company,
direction,
TYPE,
YEAR,
MONTH,
VALUE
) s
ON(t.company = s.company AND t.direction = s.direction)
WHEN MATCHED THEN
UPDATE SET t.rank = s.rank;
Add required conditions in the ON clause.
Solution 2
You can to it simply like this. It is not that commonly known but you can make UPDATE
on a SELECT
UPDATE
(SELECT
company, direction, TYPE, YEAR, MONTH, VALUE,
RANK() OVER (PARTITION BY direction, TYPE, YEAR, MONTH ORDER BY VALUE DESC) AS NEW_RANK
FROM table1) a
SET RANK = NEW_RANK;
Author by
Roy90
Updated on June 15, 2022Comments
-
Roy90 almost 2 years
There is my problem, I have a table like this:
Company, direction, type, year, month, value, rank
When I create the table, rank is 0 by default, and what I want is to update rank in the table using this select:
SELECT company, direction, type, year, month, value, rank() OVER (PARTITION BY direction, type, year, month ORDER BY value DESC) as rank FROM table1 GROUP BY company, direction, type, year, month, value ORDER BY company, direction, type, year, month, value;
This Select is working fine, but I can't find the way to use it to update table1
I have not find any answer solving a problem like this with this kind of sentence. If someone could give me any advice about if it is posible to do or not I would be very grateful.
Thanks!
-
Roy90 about 9 yearsThanks Lalit, the Merge option worked fine. In addition, it was necessary to add t.type = s.type and t.year = s.year and t.mes = s.mes and t.value = s.value in the setence ON().
-
Lalit Kumar B about 9 yearsThanks for the feedback. Yes, the joins depends on your requirements. That's why I added a note for both queries to add the required conditions.
-
Mouad_Seridi about 7 yearsThe first query has a syntax error, it's missing a parenthesis somewhere.
-
cattox about 3 years"data manipulation operation not legal on this view" is this valid for all vendors?
-
Wernfried Domscheit about 3 yearsA "view" is not a "table". A view may prevent updates, it depends how the view is defined.