?
Solved

SQL Server 2005 'NOT IN' clause probelm

Posted on 2008-06-24
9
Medium Priority
?
643 Views
Last Modified: 2013-11-30
Dear Expert,

I have a query like this:
insert into dbo.Master_Tier_Assignment
Select 'Non-Named', 'Other', 'P', 500, T.Trimed_term, '199', 500,
Cast('2008-04-01T00:00:00.001' AS Datetime) , 0.07, 0, 3, 'General', 4 from Month_trans T where T.trimed_term not in
(Select distinct Term from dbo.Master_Assignment)

Both trimed_term and Term are nvarchar(8)

When I run this query it reurns nothing  the message was (0) row effected which is not right. BUt when I take 'NOT' out just leave the 'IN' clause this query gave me coorect answer.

Please help !

John
0
Comment
Question by:johnyue
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 22

Accepted Solution

by:
dportas earned 1200 total points
ID: 21858778
Try the following. Nulls in the Term column would make a big difference.

INSERT INTO dbo.Master_Tier_Assignment
 SELECT 'Non-Named', 'Other', 'P', 500, T.Trimed_term, '199', 500,
  Cast('2008-04-01T00:00:00.001' AS Datetime) , 0.07, 0, 3, 'General', 4
 FROM Month_trans T WHERE T.trimed_term NOT IN
 (SELECT DISTINCT Term
  FROM dbo.Master_Assignment
  WHERE Term IS NOT NULL) ;
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 300 total points
ID: 21858806
Also, just as an FYI, you don't need to use DISTINCT when using a subquery, it automatically returns a distinct list for you.
0
 

Author Comment

by:johnyue
ID: 21858873
Let me try. It will take long to run because there are 138m records in my trans table
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

Author Comment

by:johnyue
ID: 21860861
Hi dportas and other expert,

I run this revised query, the result is still the same: (0) row effected. Any other ideas?

Thanks!

John
0
 

Author Comment

by:johnyue
ID: 21860984
I made a little change by add a distinct

INSERT INTO dbo.Master_Tier_Assignment
 SELECT DISTINCT 'Non-Named', 'Other', 'P', 500, T.Trimed_term, '199', 500,
  Cast('2008-04-01T00:00:00.001' AS Datetime) , 0.07, 0, 3, 'General', 4
 FROM Month_trans T WHERE T.trimed_term NOT IN
 (SELECT DISTINCT Term
  FROM dbo.Master_Assignment
  WHERE Term IS NOT NULL) ;
0
 

Author Comment

by:johnyue
ID: 21861452
Please help.  My query returns (0) row effected message.

Here is the query
NSERT INTO dbo.Master_Tier_Assignment
 SELECT DISTINCT 'Non-Named', 'Other', 'P', 500, T.Trimed_term, '199', 500,
  Cast('2008-04-01T00:00:00.001' AS Datetime) , 0.07, 0, 3, 'General', 4
 FROM Month_trans T WHERE T.trimed_term NOT IN
 (SELECT DISTINCT Term
  FROM dbo.Master_Assignment
  WHERE Term IS NOT NULL) ;
Both trimed_term and Term are nvarchar(8)
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21861690
by chance, do all of the values in the trimmed_term field in your month_trans table also exist in your master_assignment table?
0
 

Author Comment

by:johnyue
ID: 21866011
No, This is not the case.
0
 
LVL 22

Expert Comment

by:dportas
ID: 21866470
Another possibility: Are you sure that "Term" is the name of a column in dbo.Master_Assignment only. If Term exists in dbo.Master_Tier_Assignment then you may get an error or no rows returned.

If you need more help then please post enough code to reproduce the problem: CREATE TABLE statements (simplified if you like) and at least one INSERT statement to create an example row.
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question