hidross hidross - 3 months ago 19
SQL Question

Convert NOT IN query to LEFT JOIN

to adapt to Netezza DB I need to convert fallowing query(as NOT IN(SUBQUERY) is not supported by Netezza):

UPDATE table1 t1 SET t1.deal_type=t2.deal_type
FROM table2 t2
WHERE t1.id_col=t2.id_col
AND t1.price=t2.price
AND t1.id_col2=t2.id_col2
AND t2.price NOT IN (
SELECT st1.price
FROM table1 st1, table2 st2
WHERE st1.id_col=st2.id_col
AND st1.price=st2.price
AND st1.id_col2=st2.id_col2
AND st1.id_col=t1.id_col
AND t2.deal_type=st2.deal_type
GROUP BY st1.id_col, st1.price, st1.id_col2, st2.deal_type
HAVING COUNT (*)>1);


I tried with LEFT JOIN but not all records returned:

UPDATE table1 t1 SET t1.deal_type = t2.deal_type
FROM table2 t2
LEFT JOIN
(SELECT st1.price, st1.id_col, st2.deal_type
FROM table1 st1, table2 st2
WHERE st1.id_col=st2.id_col
AND st1.price=st2.price
AND st1.id_col2=st2.id_col2
GROUP BY st1.id_col, st1.price, st1.id_col2, st2.deal_type
HAVING COUNT (*)>1) subq ON (subq.id_col=t1.id_col
AND t2.deal_type=subq.deal_type)
WHERE
t1.id_col=t2.id_col
AND t1.price=t2.price
AND t1.id_col2=t2.id_col2
subq.price is null


Any suggestions where I was wrong. or any other way to work arround NOT IN witch is not supported by NETEZZA

Answer

I think you forgot to add the price to the Left Join condition.

if there are duplicates for this id&type but with different price, the NOT-IN condition will pass, but the Left-Join (IS NULL) condition will fail

just change

ON (subq.id_col=t1.id_col
   AND t2.deal_type=subq.deal_type)

to

ON (subq.id_col=t1.id_col
  AND t2.deal_type=subq.deal_type)
  AND subq.price=t2.price)