CODEWITHSUNDEEP
sqlsql-serverdatabase
mithilatw
mithilatw

Reputation: 908

SQL 'NOT IN' Operator doesn't give expected results when comparing columns in two tables

I need to update a table from a temporary table. Therefore I need to compare and find out what lines are not in the main table to be imported from the temp table. My tables look like follows,

line_id -> nvarchar(20)
order_no -> nvarchar(20)
line_no ->int

Both tables have same fields but the temp table has more up to date records to be brought to the main table. I am using;

INSERT INTO main_table
  SELECT * FROM temp_table t 
  WHERE t.line_id NOT IN (SELECT line_id FROM main_table)

But the condition WHERE t.line_id NOT IN (SELECT line_id FROM main_table) doesn't bring any order lines. But when order_no is used instead of line_id, the comparison is done and a number or order lines start to show up. But order_no is not an unique key and that comparison doesn't return all the lines needed.

It would be great if you could help me.. Thanking in advance!

Upvotes: 0

Views: 3111

Answers (2)

Rich.Carpenter
Rich.Carpenter

Reputation: 1066

Try the following:

INSERT INTO main_table
  SELECT * FROM temp_table t 
  WHERE LTRIM(RTRIMt.line_id)) NOT IN (SELECT LTRIM(RTRIM(line_id)) FROM main_table)

Upvotes: 0

David W
David W

Reputation: 10184

Not-in's can give odd troubles. Here's a different spin on the same idea. 

Insert Into main_table
  select t.* 
    from temp_table t
    left outer join main_Table m
      on t.line_id=m.line_id
   where m.line_id is null

Upvotes: 2

Related Questions