I have a table to which i have added a column varchar colorcode.
The table already has many rows. state of table after adding colorcode column is
id name location colorcode
121 Royal Challengers Bangalore
122 Sun Risers Hyderabad
123 Dare Devils Delhi
124 Gujrat Lions Ahmadabad
I have a array of color codes
["#FF8484", "#FF82A9", "#FA82FF", "#C682FF", "#8782FF"]
For each row in the table I must update the colorcode column by matching index of array with (row_number() - 1).
I have dealt with list of values when using "in" clause.
example:
select * from table where id in(1,2,3,4) etc
here 1,2,3,4 is nothing but a array
I want to update the colorcode column on similar lines but I don't know how to access the elements of my array based in index.
after running the update statement my expected output is
id name location colorcode
121 Royal Challengers Bangalore #FF8484
122 Sun Risers Hyderabad #FF82A9
123 Dare Devils Delhi #FA82FF
124 Gujrat Lions Ahmedabad #C682FF
I can sort the result based on id ie., the primary key
Note: I am using Postgres
You could do it as follows. I assume your table is called t:
update t
set colorcode = middle.cc
from (
select id, ('{#FF8484,#FF82A9,#FA82FF,#C682FF,#8782FF}'::text[])[rn] as cc
from (select id, row_number() over (order by id) as rn from t) as base
where rn <= 5
) as middle
where middle.id = t.id;
In the most inner query (base), the row number is retrieved for each record in the table. Then in the middle query (middle) that row number is used to fetch the corresponding colour code from an in-line array of text elements. Finally the update statement joins that result with the table t again, by its id, in order to store that colour code.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments