[SOLVED] Babelfish : UPDATE query with JOIN

Babelfish : UPDATE query with JOIN

I have the following sample data:

create table employee (id int,emp_name varchar(50),project_name varchar(50)); 
insert into employee(id,emp_name) values(1,'Smith');
insert into employee(id,emp_name) values(2,'Jill');
insert into employee(id,emp_name) values(3,'Hana');

create table employee_project (emp_id int,project_id int); 
insert into employee_project(emp_id,project_id) values(1,101);
insert into employee_project(emp_id,project_id) values(2,201);
insert into employee_project(emp_id,project_id) values(3,301);

create table project (id int,pro_name varchar(50)); 
insert into project(id,pro_name) values(101,'School');
insert into project(id,pro_name) values(201,'Tax');
insert into project(id,pro_name) values(301,'Road');

I need to update table employee column project_name.

Update Queries:

Try 1: Failed - Error : relation "e" does not exist

update e 
set project_name = p.pro_name 
from employee e
inner join employee_project ep on ep.emp_id = e.id
inner join project p on p.id = ep.project_id;

Try 2: Failed - Error : missing FROM-clause entry for table "p"

update employee 
set project_name = p.pro_name 
from employee e
inner join employee_project ep on ep.emp_id = e.id
inner join project p on p.id = ep.project_id;

Try 3: Works

update employee 
set project_name = p.pro_name 
from  employee_project ep 
inner join project p on p.id = ep.project_id
WHERE ep.emp_id = employee.id;

The try 3 works fine but not sure about SQL ANSI standard and also how will it gonna work for different joins(LEFT, RIGHT) when base table having number of different joins.

Note: I am running these queries from SQL Server Management Studio.

Solution

The errors you encountered occur in Babelfish v.2.3.x and before. In 2.4.0 or later, these have been fixed.