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.
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.
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
- 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
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
As far as rewriting, you might use the IN clause instead of the EXISTS clause. Something like:
CODE
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