Question

I am looking to do this:

update aTable 
set aField =  value  
where aTable.Id in (select top 400 Id from aTable order by dateField) .
-- This will run, but it only updates the first id it gets to in the IN clause

or this:

update top 400 aTable set aField =  value  order by dateField 
-- This will not run

But, it has to work in V10 of Pervasive...

A similiar workaround would be sufficient too!

Background, I am trying to update a (number(which is variable) of items) s field with a value ordered by a date field.

Here is Pervasive s Update help (I do not see the TOP reserved word in there):

UPDATE < table-name | view-name > [ alias-name ]

SET column-name = < NULL | DEFAULT
 | expression | subquery-
expression > [ , column-name = ... ] 
 [ FROM table-reference [, table-reference ] ...
 [ WHERE search-condition ]

table-name ::= user-defined-name 
 view-name ::= user-defined-name 
 alias-name ::= user-defined-name (Alias-name is not allowed if a 
FROM clause is used. See FROM Clause .)

 table-reference ::= { OJ outer-join-definition }

| [db-name.]table-name [ [ AS ] alias-name ]
 | [db-name.]view-name [ [ AS ] alias-name ]
 | join-definition | ( join-definition )
 | ( table-subquery )[ AS ] alias-name [ (column-name [ , column-name 
]... ) ]

Answer 1

I did some testing and the best way to get this working is to create a view:

create view View1 as select Id from aTable order by dateField;

Then use the view in the Update:

update aTable set aField = value where aTable.Id in (select top 400 a.Id from View1 a );

I ve tested with PSQL v11 but it should also work with PSQL v10.

友情链接