Updating Table A from Table B - 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

SQL Server Development (2000)

Updating Table A from Table B

Author

Topic

Mannga
Yak Posting Veteran

70 Posts

Posted - 2003-09-16 : 07:37:53

Hi All,

I have a list of employees in two seperate tables (I know it's not the best but it was the only way

)

When the second table was created the active column was not copied over.

What I need to do now is update all my records in table A so they match Table B.

I tried this but it doesn't work

UPDATE FirstCLass.dbo.Employees
Set FirstCLass.dbo.Employees.eActive = Frontier.dbo.Employees.Active
INNER JOIN Frontier.dbo.Employees ON FirstCLass.dbo.Employees.EmployeeID = Frontier.dbo.Employees.ID
WHERE FirstCLass.dbo.Employees.EmployeeID = Frontier.dbo.Employees.ID

I am getting the error message "Incorrect syntax near the keyword 'INNER'"?

Am I going about this completely wrong or is it something small I have missed?

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-09-16 : 07:50:00

Hi there,

you don't need your WHERE clause in there.


UPDATE FirstCLass.dbo.Employees 
Set FirstCLass.dbo.Employees.eActive = Frontier.dbo.Employees.Active 
INNER JOIN Frontier.dbo.Employees ON FirstCLass.dbo.Employees.EmployeeID = Frontier.dbo.Employees.ID

also, (I know it's not my business) but it is much easier to understand if you used aliases. Makes it easier to follow the code, but that's just me I guess!

__________________
Make love not war!

Mannga
Yak Posting Veteran

70 Posts

Posted - 2003-09-16 : 07:56:34

Hmm that does not work either, same error message.

I do agree with the alias thing but for some reason it does want to accept them in my update statement? I get "Incorrect syntax near the keyword 'As'" When I say

Update Employees As E????

mr_mist
Grunnio

1870 Posts

Posted - 2003-09-16 : 08:14:36

UPDATE FirstCLass.dbo.Employees
Set FirstCLass.dbo.Employees.eActive = Frontier.dbo.Employees.Active
FROM FirstCLass.dbo.Employees
INNER JOIN Frontier.dbo.Employees ON
FirstCLass.dbo.Employees.EmployeeID = Frontier.dbo.Employees.ID
--WHERE FirstCLass.dbo.Employees.EmployeeID = Frontier.dbo.Employees.ID

-------
Moo. :)

Mannga
Yak Posting Veteran

70 Posts

Posted - 2003-09-16 : 08:28:48

Aha, strange that you need a from clause but hey I didn't invent this stuff :)

Thanks mate ;P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources