CODEWITHSUNDEEP
sqlarrayspostgresqlpostgresql-10
Somil
Somil

Reputation: 1941

Dynamic Case statement in postgres

I am having two arrays. Both array calculated from functions so both arrays are dynamic but length of both arrays will be same.

a1= ARRAY[1,2,3];
a2= ARRAY[10,20,30];

Now I want to update my table something like this 

UPDATE TABLE SET data= CASE 
     data=a1[1] then a2[1]
     data=a1[2] then a2[2]
     data=a1[3] then a2[3]END
where id=1;

I tried with adding loop inside CASE but it is not working .

Upvotes: 1

Views: 344

Answers (1)

JGH
JGH

Reputation: 17836

You can make use of array_position to find the matching index in array 1, and query array 2 using this index:

UPDATE TABLE 
SET data = a2[array_position(a1, data)]
WHERE id = 1;

http://rextester.com/CBJ37276

Upvotes: 1

Related Questions