UPDATE "Schema" SET varLid = 'S'

FROM
(SELECT pNumber, productName, varLid, MAX(dato) FROM "Schema"
WITH (index (SepOldTempIndex))
WHERE varLid = 'T'
GROUP BY pNumber, productName, varLid HAVING MAX(dato) <= '20010101') AS myData

WHERE myData.varLid = 'T'


The DB "Schema" can have more than 1,000,000 records,
We are using pervasive SQL.
All fields (varLid, pNumber, productName etc are strings)

Running the query "as is" means that it updates all records (not just within the select) in under 6 minutes.. (its only going to be run once). Adding more conditions ie: AND Schema.pNumber = myData.pNumber makes it run extrememly slow.

I need it to update only within the select. I can't using an alias on the UPDATE (pervasive won't allow it). Finaly.. just to make this even more interesting... the DB is VERY old and does NOT have any unique identifiers.

I would be MOST grateful with any suggestions.

Thankyou in advance.

What exact version of Pervasive are you using?
What indexes are defined on the table? Indexes don't have to be unique for good performance.
Your query doesn't make a lot of sense. You're selecting several fields but only updating one. Can you give an example of the existing data and what you want it to be?

Pervasive 11 is the version.

INDEX:
CREATE INDEX "SepOldTempIndex" ON "SCHEMA"("dato" DESC, "pNumber", "productName")

The scenario is as follows:

VarLid is to be set as 'T' when the dato of the product does not exceed the selected dato.. Some products are refreshed past this date ( a continuation of sorts). If the productName has a date that continues past the selected date then it should not be included in the update.

The pNumber is a customer id of sorts.. this is not unique in that it can be repeated (still same customer... just maybe a different product) I do not have a unique ID to work with :-(.

As I said the DB is old.. and possibilities are limited... the problem comes (as far as I can see) is that the select works fine.. but it updates the entire database...

When I tried to use an alias on the update.. I was not allowed to... so if I check, for an example : WHERE myData.pNumber = Schema.Pnumber etc it is EXTREMELY slow.

Since “schema” is a meta data term, it should never be a table name. You also have not learned the ISO-11179 rules for data element names, as you said, it is a legacy database (aka “family curse” ?).

The UPDATE.. FROM.. syntax is a bad idea. Its history goes back to 1970's Sybase and pre-ANSI days. Its semantics have changed from vendor to vendor; it used to do multiple updates on the same rows.
It is a bitch to optimize and you cannot safely port your code.

>> var_lid is to be set as 'T' when the dato of the product does not exceed the selected dato. Some products are refreshed past this date (a continuation of sorts). If the product_name has a date that continues past the selected date then it should not be included in the update. <<

I would guess you will be able to use [NOT] EXISTS() and get better performance.

UPDATE "Schema"
SET varl_id = 'S'
WHERE varl_id = 'T'
AND NOT EXISTS
(SELECT *
FROM "Schema" AS S1
WHERE S1.varl_id = 'T' -- redundant
AND S1.dato > '2001-01-01' -- past date
AND ..); -- more conditions


>> The DB "Schema" can have more than 1,000,000 records [sic], We are using pervasive SQL. All fields [sic] (var_lid, p_nbr, product_name etc are strings). The DB is VERY old and does NOT have any unique identifiers. <<

Rows are not records and columns are not fields. This was designed as a paper file system and there is no way to have any DRI. You probably got that far.

Thx for the responses... ended up coding my way out of it in c# instead... I did learn to realise my utter HATE of badly designed databases from the late 90's though..

Thx again for the response.