where not clause not working...

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..."

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.

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

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.

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

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