Tricky Update Query !!!

[Connatic]

I have a query where i need to Update the Top 50 records of a resultset. The way i figured to do this was, Select the Top 50 of the records i want into a temporary table, and then update from the table.

The problem with this is that i need to Order the 50 by Date, so i am getting the 50 oldest records. The Database is in Informix, and you can't select into a temp table with an Order by clause.

To get round this i thought about making it into a sub query, but i just get Syntex Error's

SELECT Req_id
FROM Fig_Pmt_Requests
WHERE Req_id IN (SELECT FIRST 50 Req_id, req_Created  
FROM Fig_Pmt_Requests 
WHERE req_status_code = 'PP' AND Req_Type_Code IN('CL','CC','C3') ORDER BY req_Created ASC) INTO TEMP StatusIds

If i could get this first bit right then the update is easy

UPDATE Fig_Pmt_Requests 
SET req_status_code = 'PS'
WHERE Fig_Pmt_Requests.Req_id = StatusIds.Req_id

Any help would be appreciated !!!

-richard

 

[nigelrivett]

SELECT top 50 distinct Req_id
into #tmp
FROM Fig_Pmt_Requests
WHERE req_status_code = 'PP' AND Req_Type_Code IN('CL','CC','C3') ORDER BY req_Created ASC

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Connatic]

Thanks for that Nigel, but that wont work in Informix, as firstly 'top' is not a key word, you need to use 'first' and temp tables work differently !

Anybody know how i can get this to work with an Informix database ?????

Or does anybody know of a good informix forum where i could ask this question ??

 

[PHV]

Have you tried to create an index on req_Created and add req_Created > '' in the where clause to force the sql engine to browse this index ?
Take a look at SET EXPLAIN.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Connatic]

Thanks for replying so quick PH, but how can i get the WHERE clause to bring back the 50 OLDEST records with out doing an Order by ?????

WHERE req_Created = ????

If you could tell me that it would solve my problem !!!!

Cheers
Richard

 

[PHV]

Have you tried SET EXPLAIN ON; to see the query plan ?
Provided that req_Created is an indexed column, adding req_Created > '01/01/1900' may force the database engine to read in ascending date order.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Connatic]

Thanks PH, but for some reason this didn't seem to work quite right.

And finally today, the Informix DBA guy at my work had some time free & and has written me a stored procedure using Informix 4GL that does the job spot on !

Thanks Anyway, i think i learned something from this !