Select information and then update table

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)

Select information and then update table

Author

Topic

timgerr
Starting Member

2 Posts

Posted - 2007-12-27 : 11:20:54

Hello, I am new to the SQL language, I have a question for ya'all.
I am selecting data and I want to add data that is returned to a table that matches where the data came from.
I have two tables, vzn_Headcount and vzn_All. These two(2) tables have two(2) columns in common, a first name and last name column.
[vzn_Headcount].[LastName], [vzn_Headcount].[FirstName].
[vzn_All].[Last Name], [vzn_All].[First Name].

So I match these 4 colums and when I do I want to get the column
[vzn_Headcount].EMPID and insert it into [vzn_All].[EMPID] where the names match. Here is what I have so far.


SELECT
	[vzn_Headcount].EMPID
FROM
	[dbo].[vzn_Headcount] [vzn_Headcount] 
		INNER JOIN [dbo].[vzn_All] [vzn_All] 
		ON [vzn_Headcount].[LastName] = [vzn_All].[Last Name] AND
		[vzn_Headcount].[FirstName] = [vzn_All].[First Name]

and I can get [vzn_Headcount].EMPID. How can I take the returned [vzn_Headcount].EMPID and add it to vzn_All].[EMPID] when the names match?

Thanks for hearing me out,
timgerr

nr
SQLTeam MVY

12543 Posts

Posted - 2007-12-27 : 12:02:07

update vzn_All
set EMPID = [vzn_Headcount].EMPID
FROM
[dbo].[vzn_Headcount] [vzn_Headcount]
INNER JOIN [dbo].[vzn_All] [vzn_All]
ON [vzn_Headcount].[LastName] = [vzn_All].[Last Name] AND
[vzn_Headcount].[FirstName] = [vzn_All].[First Name]

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

timgerr
Starting Member

2 Posts

Posted - 2007-12-27 : 12:09:43

quote:
Originally posted by nr

update vzn_All
set EMPID = [vzn_Headcount].EMPID
FROM
[dbo].[vzn_Headcount] [vzn_Headcount]
INNER JOIN [dbo].[vzn_All] [vzn_All]
ON [vzn_Headcount].[LastName] = [vzn_All].[Last Name] AND
[vzn_Headcount].[FirstName] = [vzn_All].[First Name]

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Thank you,
timgerr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources