Where a Value does not Exist

[sms28]

My SQL is a little rusty. Table 1 (High School) contains High_school_Id and High_School_Name. Table 2 (High School Students) contains student records with High_School_Name duplicated. I want High School to contain a list of unique high school names.

I tried the following with no success.

Insert into [High School] (High_School_Name)
where not exists
(Select [High School Students] (High_School_Name);

This gives an error on the insert. I'm in Access in the SQL view. What's wrong?

 

[gfjzr]

sms28, if I understand the problm correctly, you want to populate the high school table with unique records that do not already exist and are in the high school students table. If that is the case try this:

insert into high school
select high_school_name
from high school
where not exists (select * from high school students where high school students.high_school_name = high school.high_school_name)

A couple issues I see are: Table names with out underscores, tabel design is not correct. high school name should not be duplicated. use the id from the high school table as a foriegn key in the high school student table. Also the name high school student table should be renamed student with a column indicating type of student.

Just some thoughts.

Gary

 

[hneal98]

I think it should be similar to this:


Insert into [High School].[High_School_Name]
Select distinctrow [hss].[High_School_Name]
from [High School Students] hss
Left join [High School] hs on [hss].[High_School_ID] = [hs].[High_School_ID]
where
[hss].[High_School_Name] is Null;

 

[Lekar]

How does DistinctRow works?
Which version of SQLServer are you using?