My UPDATE statement with WHERE EXISTS does not limit to the SELECT statement results

Question

I have data in a temporary table and I am checking for duplicates in two other tables. I want to set a flag on the temp table (SET DupFlag = TRUE) for all duplicates found. My SELECT statement works perfectly, returning only the 48 duplicates that I entered to test with. But when I add UPDATE and WHERE EXISTS, every record in idTempImport2 is set to TRUE instead of just the 48 records returned from the SELECT statement. Is my syntax wrong? It looks like this:

UPDATE idTempImport2 as tmp2 SET DupFlag = TRUE
WHERE EXISTS
(SELECT * FROM idTempImport2 tmp2
LEFT JOIN (SELECT im.idDate, im.UserID, im.ActionID, im.IsHC, idn.Epic1, idn.Epic2 
      FROM idMain AS im 
      INNER JOIN idNotes AS idn ON im.aID = idn.MainID 
     WHERE idDate BETWEEN "2017-01-02" AND "2017-01-31")  AS qry 
ON qry.idDate = tmp2.idDate AND qry.UserID = tmp2.UserID AND qry.ActionID = tmp2.ActionID AND qry.Epic1 = clng(tmp2.Epic1) AND qry.Epic2 = clng(tmp2.Epic2)
WHERE qry.Epic1 <> NULL);

You are aware that exists will pass with nulls? http://www.techrepublic.com/article/oracle-tip-understand-how-nulls-affect-in-and-exists/ http://stackoverflow.com/questions/5658457/not-equal-operator-on-null — user2184057, Mar 29 '17 at 15:29
qry.Epic1 <> NULL here is your error never use equality <> = != operators with NULL. When comparing to nulls use IS NULL AND IS NOT NULL — user2184057, Mar 29 '17 at 15:32
Does that mean the update will not be limited to the returned results in the select statement? — Ginger, Mar 29 '17 at 15:33

score 1 · Accepted Answer · answered Mar 29 '17 at 15:32

I think the ultimate issue is that you want a correlated subquery. As written, the subquery has no connection to the outer query, so it is likely that at least one row meets the condition. So, everything gets updated.

I think you intend:

UPDATE idTempImport2 as tmp2
    SET DupFlag = TRUE
WHERE EXISTS (SELECT im.idDate, im.UserID, im.ActionID, im.IsHC, idn.Epic1, idn.Epic2 
              FROM idMain AS im INNER JOIN
                   idNotes AS idn
                   ON im.aID = idn.MainID 
              WHERE idDate BETWEEN "2017-01-02" AND "2017-01-31" AND
                    im.idDate = tmp2.idDate AND im.UserID = tmp2.UserID AND
                    im.ActionID = tmp2.ActionID AND
                    ?.Epic1 = clng(tmp2.Epic1) AND ?.Epic2 = clng(tmp2.Epic2) 
             );

That works perfect, thank you Gordon for your help! And thank you for explaining it. — Ginger, Mar 29 '17 at 15:40

My UPDATE statement with WHERE EXISTS does not limit to the SELECT statement results

1 Answers1