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.
SQL Update Query
mindblower
You have LaborEmployee in the Update twice and you only need it once. Using the name on the Update and again in the From is okay -- they are treated as being the same table. Alternatively, you can use a table alias.
Update LaborEmployee
Set grade = db2..gradestep.grade,
step = db2..gradestep.step
From LaborEmployee
Inner Join db2..gradestep
ON LaborEmployee.Hourly = db2..gradestep.payrate
where LaborEmployee.EmployeeName like 'link%'
Update le
Set grade = gs.grade,
step = gs.step
From LaborEmployee as le
Inner Join db2..gradestep as gs
ON le.Hourly = gs.payrate
where le.EmployeeName like 'link%'
Encoder99
Nevermind...
I had LabourEmployee defined in my FROM twice..
Thanks for looking.
JD