SQL Server Performance

where not clause not working...

Discussion in 'General Developer Questions' started by ramkumar.mu, Oct 18, 2006.

  1. ramkumar.mu New Member

    INSERT INTO tbl_gl_code_description
    SELECT b.gl_code
    ,a.upc_description
    ,'Y'
    FROM tbl_upc a
    join tbl_expense_description_details b
    on a.expense_id = b.expense_id
    join tbl_gl_code_description c
    on b.gl_code = c.GL_Code
    WHERE a.status IN ('CREATED','AMENDED')
    AND NOT EXISTS (SELECT 1 FROM tbl_gl_code_description_backup2 x
    WHERE x.gl_code = b.gl_code
    AND ltrim(rtrim(x.gl_description)) = ltrim(rtrim (a.upc_description))
    AND x.modify_ind = 'y')
    ORDER BY gl_description

    The above code inserts duplicate values inspite of that where not exists clause. THe destination table tbl_gl_code_description doesnt have any values but the select query has duplicates.
    my question is, does "where not exists" doesnt eliminate the duplicate values in the select query?

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  2. Adriaan New Member

    Use SELECT DISTINCT ..... to avoid the duplicates, or SELECT ..... GROUP BY ......

    The duplicates are probably coming from the joined tables in the main query: there are multiple identical combinations of (b.gl_code, a.upc_description) given the matching on the columns on which you joined the tables.
  3. Adriaan New Member

    Ah yes - the NOT EXISTS clause is evaluated for the data before the insertion, not during the insertion.
  4. mmarovic Active Member

    quote:Originally posted by ramkumar.mu

    INSERT INTO tbl_gl_code_description
    SELECT b.gl_code
    ,a.upc_description
    ,'Y'
    FROM tbl_upc a
    join tbl_expense_description_details b
    on a.expense_id = b.expense_id
    join tbl_gl_code_description c
    on b.gl_code = c.GL_Code
    WHERE a.status IN ('CREATED','AMENDED')
    AND NOT EXISTS (SELECT 1 FROM tbl_gl_code_description_backup2 x
    WHERE x.gl_code = b.gl_code
    AND ltrim(rtrim(x.gl_description)) = ltrim(rtrim (a.upc_description))
    AND x.modify_ind = 'y')
    ORDER BY gl_description

    In case the table tbl_gl_code is realy empty, select would return nothing. So better check again. Something is messed up with your problem description.
  5. Adriaan New Member

    Well spotted, Mirko. But he still needs to use DISTINCT or GROUP BY.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  6. mmarovic Active Member

    Sure, it doesn't hurt to apply them on empty row set [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]

Share This Page