×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

UPDATE based on joined tables

thread318-1500734

UPDATE based on joined tables

UPDATE based on joined tables

(OP)
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.

RE: UPDATE based on joined tables

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

RE: UPDATE based on joined tables

(OP)
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.

RE: UPDATE based on joined tables

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
http://www.mirtheil.com

RE: UPDATE based on joined tables

(OP)
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.
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close