NOT EQUAL TO join problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

NOT EQUAL TO join problem

Author

Topic

Entropy
Starting Member

1 Post

Posted - 2003-05-08 : 22:58:21

Hi,

I’m currently writing a series of SQL statements to help me update my database when I receive a new data dump. I receive information both online and from an employee system. The information I receive online (which is stored in the AHW table) is for the whole data set I use to report on. However, the data I receive from the employee system contains only a few fields that I want to update into the existing records. Once I’ve updated the existing records, I want to add in as new records all records from the employee system that aren’t currently in the AHW table. Both tables share an EmpID number I use to join.

The order of tasks is like this -->

1. Move existing AHW data to a temporary table
2. Delete all records in the AHW table
3. Compare the employee system data with that in the temporary table – if there are matches on EmpID, insert the temptable record, but with the updated fields from the employeesystem.
4. Compare the employee system data with that in the temporary table – Add all employeesystem.rows from the employee that are not listed in the temporary table.

Step 4 is what I’m having difficulty with. The rough code should be:

Insert into AHW SELECT employeesystem.EmpID, employeesystem.otherfields WHERE Employeesystem.EmpID NOT EQUAL TO AHW.EmpID

The “NOT EQUAL TO” is not recognised by SQL. Does anyone have any suggestions?

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-05-08 : 23:04:36

Entropy

You are After NOT EXISTS


Insert into AHW 
SELECT E.EmpID, E.otherfields
FROM Employeesystem E
WHERE NOT EXISTS(Select 1 from AHW WHERE EmpID = E.EmpID)

DavidM

"SQL-3 is an abomination.."

shifis
Posting Yak Master

157 Posts

Posted - 2003-05-08 : 23:21:05

I read something about the NOT IN funtion, so i guess it could be

Insert into AHW
SELECT employeesystem.EmpID,
employeesystem.otherfields
FROM Employeesystem
WHERE Employeesystem.EmpID NOT IN ( SELECT AHW.EmpID
FROM AHW
)

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-09 : 12:32:10

NOT IN and NOT EXISTS work differntly.

It all depends what you're trying to do...

In your example NOT IN will first return All of the rows in the sub query (not good in this case), Where as NOT EXISTS will coorelate them and only get the rows it needs.

Brett

8-)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-09 : 12:41:31

quote:

In your example NOT IN will first return All of the rows in the sub query (not good in this case), Where as NOT EXISTS will coorelate them and only get the rows it needs.

Nope.

The reason you should be careful is that, in the presence of NULLs, they mean different things (see threads passim).
e.g. http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=23375

Edited by - Arnold Fribble on 05/09/2003 12:47:00

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-09 : 14:01:12

Arnold,

I get the Null part, but doesn't

SELECT * FROM Table1 Where Col1 NOT IN (SELECT Col1 From Table2)

Return all The rows in Table2 First? Then takes the first row in Table1 and scan the result set of table2? And So on?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-09 : 17:23:25

I prefer LEFT OUTER JOIN ... call me old fashioned.

Select a.empID
from
a
left outer join
b
on a.empID = b.empid
where b.empID is null

looks like you have lots of options !

By the way, no need for steps 1 and 2 or 3. just use an update query and then an append:

update ahw
set field1 = new.field1, field2 = new.field2
from ahw
inner join
new
on
ahv.empID = new.empID

and then do your INSERT query using any of the methods listed. much easier on your transaction log as well.

BTW -- what happens if data is deleted? do you want to delete those rows to keep things in synch?

if you use your steps 1-4 that you specified, there is no need for any of the tricky stuff. just empty the table, and insert all data from the new table. all set!

- Jeff

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-09 : 17:39:58

Ok, "your Outer Join and Filter is much less flashy and modern than a Anti Semi Join"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources