I wonder, if the subquery in the following update statement is a good (not correlated) or bad (subquery)?
In other words, my question is, is it an inefficient query?
UPDATE tableA
SET field1=0
FROM tableA
WHERE field2 IN (SELECT field2
FROM tableA
WHERE someField IS NOT NULL
AND someOtherField = 'ABC')
Your query is not correlated ,its just a subquery..
below is a correlated subquery..
UPDATE a
SET field1=0
FROM tableA a
WHERE exists (SELECT 1
FROM tableB b
WHERE a.somecol=b.somecol)
one more example of correlated subquery
select orderid,
(select custname from customers c where c.custid=o.custid) from orders o
above query can be written as join
select orderid,custname
from orders o
join
customers c
on c.custid=o.custid
executing both queries tend to use same execution plan and both have same cost as well..so we can't assume,Correlated subqueries won't perform better
select orderid,
(select count(orderid) from orders o2 where o2.custid=o.custid )
from orders o
for the above correlated subquery,SQL can't access orders table only once and do all the calculations,it will need to access table twice..this is only gotcha i could see with correlated subqueries
Subqueries are not inherently good or bad (one could argue rather that SQL optimizers are bad, rather than subqueries). Your example is not correlated at all.
The question of whether a particular query is efficient or not requires analyzing the execution plan. That, in turn, mostly depends on the distribution of data, indexes, and partitioning schemes for the data. There is nothing a priori bad about your query.
There are other ways to write the logic. I like this one:
WITH toupdate as (
SELECT a.*,
SUM(CASE WHEN someField IS NOT NULL AND someOtherField = 'ABC'
THEN 1 ELSE 0
END) OVER (PARTITION BY field2) as cnt
FROM tableA a
)
UPDATE toupdate
SET field1 = 0
WHERE cnt > 0;
This is not 100% the same. One difference is that this treats a NULL value in field2 just like any other value; your version will never update a NULL value.
field2 is NULL will never be updated.