SQL Update Query - 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)

SQL Update Query

Author

Topic

jdegaetani
Starting Member

9 Posts

Posted - 2006-02-08 : 11:19:56

The scenario:
I have two tables in two dbs on the same 2000 sp3a SQL server.

My LaborEmployee table in db1 has HourlyRate, grade & step fields. My GradeStep table in db2 has grade, step & payrate fields. When I run;

Use db1
SELECT LaborEmployee.Hourly, LaborEmployee.grade, LaborEmployee.step, LaborEmployee.EmployeeName, db2..gradestep.payrate
FROM LaborEmployee, db2..gradestep
where LaborEmployee.Hourly = db2..gradestep.payrate

I get good results.
But I actually what to update the grade and step in LaborEmployee where LaborEmployee.Hourly = db2..gradestep.payrate
I try this on one employee using the this query;

Use db1
Update LaborEmployee
Set grade = db2..gradestep.grade,
step = db2..gradestep.step
From LaborEmployee,
db2..gradestep
INNER JOIN LaborEmployee
ON LaborEmployee.Hourly = db2..gradestep.payrate
where LaborEmployee.EmployeeName like 'link%'

but I get;

Tables or functions 'LaborEmployee' and 'LaborEmployee' have the same exposed names. Use correlation names to distinguish them.

Can you please direct me on correcting my lame syntax?
Thanking you in advance for your help.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-08 : 11:40:52

[code]
Use db1
Update LaborEmployee
Set grade = db2..gradestep.grade,
step = db2..gradestep.step
From db2..gradestep
INNER JOIN LaborEmployee
ON LaborEmployee.Hourly = db2..gradestep.payrate
where LaborEmployee.EmployeeName like 'link%'
[/code]

You had LabourEmployee defined in your FROM twice..

jdegaetani
Starting Member

9 Posts

Posted - 2006-02-08 : 11:47:37

Thank you very much for the second set of eyes.
JD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources