UPDATE based on joined tables

[BEVer69]

I am relatively new to SQL and would appreciate your insight on formulated the following statement.

I have two tables representing the header (ek2122) and detail items (ek2124) of a purchase order. I would like to update a value in the details based on a join between the two tables. Here is what I have so far but I don't think Pervasive 8.5 handles "WHERE EXISTS" well in an UPDATE statement. The syntax is correct but the statement never completes.

update ek2122
set ek2122_lager = '2362'
where exists
	(select * from ek2122
	inner join ek2124
	on ek2122_bs_nr = ek2124_bs_nr
	where ek2124_ret_grund = '100'
	and ek2122_lager = '2360')

Your help is greatly appreciated.

 

[mirtheil]

A few questions:
- How long have you let it run?
- How many records does the SELECT return?
- How long does it take to return the records on the SELECT?
- Have you considered updating to PSQL 8.7 (or ideally v9/v10)?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[BEVer69]

The result set is around 700 records. A similar select query using the same join took about 5 seconds. I let the above query run for more than an hour and it still did not complete.

Can you suggest a way to rewrite this query to avoid the WHERE EXISTS? Perhaps this is the culprit.

I am unable to update my production server at this time but can try v10 Summit in a test system tomorrow if you think it is a version issue.

 

[mirtheil]

Did you see any activity in the PSQL Monitor while the query was running? Specifically, disk or cache accesses on the data file (or files) in the Active Files section under Microkernel.
As far as rewriting, you might use the IN clause instead of the EXISTS clause. Something like:

update ek2122
set ek2122_lager = '2362'
where in
    (select * from ek2122
    inner join ek2124
    on ek2122_bs_nr = ek2124_bs_nr
    where ek2124_ret_grund = '100'
    and ek2122_lager = '2360')

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[BEVer69]

The original syntax was used in Pervasive PSQL v10 and completed successfully within a few minutes. I tried the suggested change using "where in" but this does not seem to be supported. Many thanks for your help Mirtheil in recognizing that v8 was the issue.