CODEWITHSUNDEEP
postgresql
John Mitchell
John Mitchell

Reputation: 281

How would I have a field updated sequentially based on a defined where clause order

I want to be able to update a field sequentially based on the order that it was put in the where in ('[john2].[john2]','[john3].[john3]','[john].[john]') clause but it does not appear to update based on its associated order (see SQL below). How would I have a field updated sequentially based on a pre-defined where clause order?

John

drop sequence temp_seq;
 create temp sequence temp_seq;

update gis_field_configuration_bycube
 set seq_in_grid = nextval('temp_seq')
 where cube = 'Instruments' and level_unique_name in ('[john2].[john2]','[john3].[john3]','[john].[john]');

Upvotes: 1

Views: 206

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61706

The order is irrelevant in an IN() construct because it defines a set, not a list of values in a strict sense. The VALUES clause is what should be used instead.

Also, assuming Postgres 8.4 or better, row_number() would be less cumbersome than creating a temporary sequence.

Here's what should work:

update gis_field_configuration_bycube
 set seq_in_grid = rn
from 
(select  row_number() over () as rn , string from
  (values ('[john2].[john2]'),('[john3].[john3]'),('[john].[john]')) as v(string)
) as subquery
 where cube = 'Instruments'
 and level_unique_name=subquery.string;

Upvotes: 1

Related Questions