Creating new Table, filling it with data from other tables

[vonehle]

I'm having trouble figuring out how to fill a table with data from 3 other tables. Here's a simple example...

CREATE TABLE dbo.Transcripts( StudentID int, First_Name CHAR(20), Last_Name CHAR(20),
							Semester int, ClassID CHAR(5), 
							Title CHAR(50), Grade int,
							Credits int, GPA float )

GO

INSERT INTO dbo.Transcripts(StudentID, ClassID, Grade, Semester)
	Select StudentID, ClassID, Grade, Semester FROM dbo.Enrollment
	Where Semester<>0
GO

INSERT INTO dbo.Transcripts(First_Name)
	Select First_Name from dbo.Students
	Where dbo.Transcripts.StudentID = dbo.Students.StudentID

The problem occurs on the second INSERT section. I'm trying to match the StudentID to fill in the correct name. I also tried using an UPDATE there. The error I get says "The multi-part identifier "dbo.Transcripts.StudentID" could not be bound.

 

[jbenson001]

You need to use an update statment here with a Join.. but the error is because you don't need the dbo.tablename

somehthing like:
Update Transcripts
set col = something
from Transcripts t
Inner Join Students s ON
t.StrudentID = s.StudentID

 

[vonehle]

I'm not sure I understand how I could use the UPDATE because the SET would be different every time. Isn't SET static?

Thanks for your help. I'm going to read up on the JOIN stuff again.