Sql Server equivalent Join update Oracle

Question

My update query Sql Server as follows:

Update Project set Name=Project.Name+' assignedTo '
FROM Project 
INNER JOIN EmployeeProject ON Project.ID = EmployeeProject.ProjectID 
INNER JOIN Employee ON EmployeeProject.EmployeeID = Employee.ID
WHERE (Employee.Name = N'Minion')

what is equivalent Oracle query.My try as follows:

Update (SELECT Project.Name as ProjectName,Employee.Name as EmpName
        FROM Project 
        INNER JOIN EmployeeProject ON Project.ID = EmployeeProject.ProjectID 
        INNER JOIN Employee ON EmployeeProject.EmployeeID = Employee.ID  
        where Employee.Name=N'Minion' 
       ) T 
set T.ProjectName=' somting'

But gives me error cannot modify a column which maps to a non key-preserved table.Sql server works fine.

@ a_horse_with_no_name can you rather post an answer – decoder Jul 07 '17 at 09:03 — decoder, Jul 07 '17 at 09:03

score 1 · Accepted Answer · answered Jul 07 '17 at 01:52

You just need exists:

update Project p
    set Name = p.Name+' assignedTo '
where exists (select 1
              from EmployeeProject join 
                   Employee e
                   on ep.EmployeeID = e.ID
              where e.Name = N'Minion' and p.ID = ep.ProjectID 
             );

Sql Server equivalent Join update Oracle

1 Answers1