Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

UPDATE based on joined tables

Status
Not open for further replies.
BEVer69

BEVer69

IS-IT--Management
Apr 9, 2008
9
CA
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.

Code: 
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.
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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:
Code: 
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Tabled
  • Locked
  • Question
Periodically run an SQL update statement 1
Replies
9
Views
751
mirtheil
mirtheil
bojimuncher
  • Locked
  • Question
Linking tables crashing Pervasive 1
Replies
7
Views
533
mirtheil
mirtheil
mSportsman
  • Locked
  • Question
C# Pervasive Data Provider Update command causing Syntax error, but not on development machine.
Replies
5
Views
409
mirtheil
mirtheil
soocallmebrady
  • Locked
  • Question
Pervasive SQL Update with Joins - Error: Unknown table or correlation name
Replies
0
Views
252
soocallmebrady
soocallmebrady
DWest1965
  • Locked
  • Question
Set command in script does not work on Pervasive V11
Replies
3
Views
287
mirtheil
mirtheil

Part and Inventory Search

Sponsor

Back
Top