Correlated Sub-queries

update customers set stat = “A”

where exists (

select “X”

from orders o

where o.custid = customer.custid

and o.cmpny = customers.cmpny

and o.stat = “OPEN” )

and custid in (

select custid

from orders o

where o.stat = “OPEN” )

update customers set stat = “A”

where exists (

select “X”

from orders o

where o.custid = customers.custid

and o.cmpny = customers.cmpny

and o.stat = “OPEN” )

If orders has only 20 rows meeting the filter, the second version of the update runs much faster, assuming that customers has an index on the column custid.

The orginal CSQ is left since it was joining on more than one column

Previous slide

Next slide

Back to first slide

View graphic version