CODEWITHSUNDEEP
sqljoinnesteddb2
Riddhi Patil
Riddhi Patil

Reputation: 15

How to convert nested sql query to join?

Please help me rewrite below query using join:-

update "+tableName+" set F_RK='T' 
where ISN in ( 
    SELECT DISTINCT B.ISN 
    FROM "+tableName+"_PE_BA AS B,"+regisTableName+" AS C, F0093 AS D  
    WHERE B.F_BB = C.F_AB
    AND C.F_FB = D.F_AB
    AND D.F_OA = 1 
    AND C.F_FC = (SELECT MAX(E.F_FC) FROM "+regisTableName+" AS E WHERE E.F_AB = C.F_AB)) 
and F_RK is null or F_RK=''";

Also, I want to optimize the query.

Upvotes: 0

Views: 137

Answers (1)

JohnHC
JohnHC

Reputation: 11205

For optimisation, consider a where exists (may or may not improve performance). For readability, use explicit joins.

Update MyTable
set F_RK = 'T'
where exists
(
select 1
from Table2 B
inner join Table3 C
  on B.F_BB = C.F_AB
inner join Table3 D
  on C.F_FB = D.F_AB
  AND D.F_OA = 1 
where C.F_FC = (SELECT MAX(E.F_FC) FROM Table4 E WHERE E.F_AB = C.F_AB)
)
or F_RK is null 
or F_RK=''

Upvotes: 1

Related Questions